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

 

[Fluff]

The formula in post#23 returns the same values as your formulae & from what you have said they are correct.

 

[juca73]

The formula in post#23 returns the same values as your formulae & from what you have said they are correct.

Thank you so much for your time, it all appears to be working correctly, the semi colon was the answer.

 

[Fluff]

Glad to help & thanks for the feedback