SUM & SUMIFS formula not returning correct value

[juca73]

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]

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?

 

[Fluff]

That's right.

 

[Fluff]

You could simplify things by putting this in X9 & fill down

=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]

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

=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]

Are you trying it in 365 or 2010?

 

[juca73]

Are you trying it in 365 or 2010?

2010 but have got 365 too, will try that

 

[Fluff]

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

 

[juca73]

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]

In that case stick to your formulae.

 

[juca73]

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