Nov 10 2020 06:57 PM - edited Nov 10 2020 07:03 PM
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.
Nov 10 2020 09:03 PM - edited Nov 10 2020 09:05 PM
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.
Nov 11 2020 01:00 AM
Nov 15 2020 07:38 AM
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.
Nov 15 2020 07:41 AM
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.
Nov 15 2020 12:17 PM
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.
Nov 15 2020 11:42 PM
SolutionNov 15 2020 11:42 PM
Solution