# SUM & SUMIFS formula not returning correct value

#### juca73

##### New Member
Hi

I am using the following formula

=SUM(SUMIFS(Data!\$AG\$7:\$AG\$1048576,Data!\$D\$7:\$D\$1048576,Sheet1!\$F\$2,Data!\$O\$7:\$O\$1048576,">="&\$D40,Data!\$O\$7:\$O\$1048576,"<="&\$E40,Data!\$M\$7:\$M\$1048576,{"Amber","Green"},Data!\$N\$7:\$N\$1048576,{"Amber","Red"}))

The value returned is not the same as when i filter it on the data sheet

Sheet 1 = returns 15.00
Data Sheet = returns 10.28

There were some blank cells in column AG on the data sheet, i have inserted 0.00, thinking
the blank cells would be causing the fault, turns out not to be the case

Any ideas?

Thanks

#### juca73

##### New Member
So my formula is good apart from substituting the commas for semi colons in the 2nd array?

Yet retaining the commas in the 1st array?

### Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

That's right.

#### Fluff

##### MrExcel MVP, Moderator
You could simplify things by putting this in X9 & fill down
Excel Formula:
=SUM(SUMIFS(Data!\$J\$4:\$J\$27,Data!\$D\$4:\$D\$27,\$P\$7,Data!\$I\$4:\$I\$27,">="&\$N9,Data!\$I\$4:\$I\$27,"<="&\$O9,Data!\$G\$4:\$G\$27,Q9:S9,Data!\$H\$4:\$H\$27,TRANSPOSE(T9:V9)))

#### juca73

##### New Member
So my formula is good apart from substituting the commas for semi colons in the 2nd array?

Yet retaining the commas in the 1st array?

You could simplify things by putting this in X9 & fill down
Excel Formula:
=SUM(SUMIFS(Data!\$J\$4:\$J\$27,Data!\$D\$4:\$D\$27,\$P\$7,Data!\$I\$4:\$I\$27,">="&\$N9,Data!\$I\$4:\$I\$27,"<="&\$O9,Data!\$G\$4:\$G\$27,Q9:S9,Data!\$H\$4:\$H\$27,TRANSPOSE(T9:V9)))
Just tried that formula, no values change in column X

there should be some values when i change min to 2.5 and max to 3.0 and league to eliteserien

#### Fluff

##### MrExcel MVP, Moderator

Are you trying it in 365 or 2010?

#### juca73

##### New Member
Are you trying it in 365 or 2010?
2010 but have got 365 too, will try that

#### Fluff

##### MrExcel MVP, Moderator

With 2010 you may need to confirm it with Ctrl Shift Enter, rather than just Enter.

#### juca73

##### New Member
ok tried it in 365, however there should only be one value listed

eliteserien amber v amber = -2.00 which is correct

i do have -2.00 values on other rows where amber is present in the columns

#### Fluff

##### MrExcel MVP, Moderator
In that case stick to your formulae.

#### juca73

##### New Member

The highlighted figure should be 0.00, the only one thats correct is the one not highlighted

Replies
1
Views
56
Replies
3
Views
60
Replies
6
Views
260
Replies
5
Views
106
Replies
8
Views
77

1,127,623
Messages
5,625,940
Members
416,143
Latest member
JoyceMB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back