Simple sumif formula only adds one cell

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.




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

8 Replies



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.


Practically the same


@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.



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.


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.

best response confirmed by swiftpen (New Contributor)
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 !!!
I like it. And - Thanks so much.
Glad to help you,,, keep asking ☺