SOLVED

Simple sumif formula only adds one cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1874898%22%20slang%3D%22en-US%22%3ESimple%20sumif%20formula%20only%20adds%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1874898%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20formula%20is%20simple%20and%20I've%20even%20tried%20retyping%20in%20into%20a%20new%20clean%20spreadsheet.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20cell%20F7%20is%20equal%20to%20Y%2C%20add%20cells%20A4%3Ad4.%26nbsp%3B%20Sounds%20simple%20to%20me%2C%20but%20it%20ignores%20cells%20B4%2C%20c4%2C%20and%20d4%20and%20only%20adds%20A4.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22465%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2295px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3Ey%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eformula%20is%20%3DSUMIF(F7%2C%22n%22%2CA7%3AD7)%26nbsp%3B%20I've%20reformatted%20for%20numbers.%26nbsp%3B%20Nothing%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1874898%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1874963%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20sumif%20formula%20only%20adds%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1874963%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864786%22%20target%3D%22_blank%22%3E%40swiftpen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESUMIF%20considers%20cell%20to%20cell%20value%2C%2C%20you%20have%20SUM%20rage%20A2%3AD2%20has%204%20values%2C%20and%20comparing%20to%20CRITERIA%20has%20only%20ONE%20Value%20in%20F2%2C%20so%20that%20formula%20picks%20only%20one%20Cell%20to%20SUM%2C%20is%20A2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20the%20attached%20file%20I've%20suggested%20few%20other%20options.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1875565%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20sumif%20formula%20only%20adds%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1875565%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864786%22%20target%3D%22_blank%22%3E%40swiftpen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPractically%20the%20same%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(A2%3AD2)*(F2%3D%22y%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1888331%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20sumif%20formula%20only%20adds%20one%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1888331%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%26nbsp%3B%20works!%26nbsp%3B%20%26nbsp%3BI%20was%20on%20a%20tech%20support%20chat%20with%20Microsoft%20for%20about%20an%20hour%20and%20they%20agreed%20my%20simple%20formula%20should%20have%20worked%20and%20it%20didn't%20work%20on%20their%20end%20either.%26nbsp%3B%20They%20too%20gave%20me%20a%20workaround%20of%26nbsp%3B%26nbsp%3B%3DIF(I6%3D%22Y%22%2CE6%2CSUM(E6%3AG6))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20just%20weird%20that%20the%20first%20simple%20sumif%20didn't%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

My formula is simple and I've even tried retyping in into a new clean spreadsheet.  

 

If cell F7 is equal to Y, add cells A4:d4.  Sounds simple to me, but it ignores cells B4, c4, and d4 and only adds A4.

 

23502y

 

formula is =SUMIF(F7,"n",A7:D7)  I've reformatted for numbers.  Nothing helps.

8 Replies
Highlighted

@swiftpen 

 

SUMIF considers cell to cell value,, you have SUM rage A2:D2 has 4 values, and comparing to CRITERIA has only ONE Value in F2, so that formula picks only one Cell to SUM, is A2.

 

Check the attached file I've suggested few other options.

Highlighted

@swiftpen 

Practically the same

=SUM(A2:D2)*(F2="y")
Highlighted

@Sergei Baklan 

 

That  works!   I was on a tech support chat with Microsoft for about an hour and they agreed my simple formula should have worked and it didn't work on their end either.  They too gave me a workaround of  =IF(I6="Y",E6,SUM(E6:G6))

 

It's just weird that the first simple sumif didn't work.

Highlighted

@Rajesh-S 

 

That make sense, but I have data in the four cells to the right of the criteria cell which is all numbers.  Since there will be no "Y" in those, it may work.  I'll check that out.

Highlighted

@swiftpen 

In general if you use SUMIF() all ranges shall be of the same size. If sumRange consists of 4 cells, criteriaRange also shall have 4 cells.

Highlighted
Best Response confirmed by swiftpen (New Contributor)
Solution
I've suggested correct formula using the data from the attached file by you only,,, and both are working,,, check the sheet 1,,, Y is in cell F2 !!!
Highlighted
I like it. And - Thanks so much.
Highlighted
Glad to help you,,, keep asking ☺