Excel - sumif not updating properly

  • Thread starter Thread starter stoneskimmer
  • Start date Start date
S

stoneskimmer

I built sumif formulas into a workbook. Pretty basic formulas, the
sumif's are based on values within the workbook, i.e., not another
workbook.

The sumif's are built something like this:

The formula in cell D105 on the "Actual Input" tab =

=SUMIF('Actual~Input'!$C$5:$CL$5,'Data Input'!D$2,'Actual~Input'!
$C105:$CL105)

When I change the value in the range 'Actual~Input'!$C$5:$CL$5, which
I will refer to as the "control area", the cell value changes.

When another co-worker changes the values in the control area, nothing
updates.

I tested this with a third co-worker and the cells values change when
she makes changes in the "control area".

Any ideas as to why this won't update? The auto calc is turned on.
This has me stumped!

Thanks
S
 
It could be data consistency, where the presence of extraneous white spaces
throws the matching off
Try this equivalent sumproduct, with TRIM added for improved robustness:
=SUMPRODUCT(--(TRIM('Actual~Input'!$C$5:$CL$5)=TRIM('Data
Input'!D$2)),'Actual~Input'!$C105:$CL105)
 
When another co-worker changes the values in the control area,
nothing updates.
Click to expand...

Maybe this person has a habit of pressing the spacebar after each word,
irregardless. TRIM will take care of that, but it's not allowed in SUMIF.
Hence the sumproduct suggestion earlier.

---
 
Back
Top