Simple Sumif giving wrong results

[santidreamer]

I am new to exce1 2011 formulas and cannot see why this formula is going wrong.

G53=SUMIF(B:B,"Yes",G2:G51)

The Yes in column B is generated by the formula below (which works)

=IF(AND(C23="",H23=""),"Yes","")

The value returned in G53 = 13,550,000

It should be 3,995,00+3,995,000+3,750,000+3,750,000 =15,490,000

15,490,000-13,550,000= 1,940,000 which is 1,940,000 (which is much less than any G2:G51 value.

Thank you in advance for helping

 

[santidreamer]

I have found the error

When B48=Yes G49 is added.

This is also true for the other 4 Yes results.

My understanding of this function is that when B48=Yes G48 is added.

 

[scottylad2]

G53=SUMIF(B:B,"Yes",G2:G51) your issues will be caused by your arrays being different sizes, you have whole column B yet only G2:G51 for your sum range. Make both the same dimension and your issue will be resolved

 

[barry houdini]

You have specified the whole column as the criteria range (B:B) but G2:G51 as the sum range. Doing that means that if the 1st cell in B:B is "Yes" then the first cell in G2:G51 is added to the sum, if B2 is "Yes" G3 is added etc.

In fact the sum range is automatically the same size as the criteria range so your formula can sum cells right down column G, e.g. G1000 will be added to the result if B999 is "Yes". Change to either

=SUMIF(B:B,"Yes",G:G)

or

=SUMIF(B2:B51,"Yes",G2:G51)

 

[santidreamer]

Thank you both so much.

Oh dear I have a lot to learn, thought I understood this function