 SOLVED

New Contributor

# Simple sumif formula only adds one cell

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.

 2 3 5 0 2 y

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

8 Replies

# Re: Simple sumif formula only adds one cell

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.

# Re: Simple sumif formula only adds one cell

Practically the same

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

# Re: Simple sumif formula only adds one cell

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.

# Re: Simple sumif formula only adds one cell

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.

# Re: Simple sumif formula only adds one cell

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)
Solution

# Re: Simple sumif formula only adds one cell

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 !!!

# Re: Simple sumif formula only adds one cell

I like it. And - Thanks so much.

# Re: Simple sumif formula only adds one cell

Glad to help you,,, keep asking ☺