SUM & SUMIFS formula not returning correct value

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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?
 

Some videos you may like

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.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
That's right. :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you trying it in 365 or 2010?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
In that case stick to your formulae.
 

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
sheet10.PNG


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

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top