I'm trying to subtotal a column if another column contains specific text. The goal is to get a new total in column B if column A contains "--", otherwise the total should be 100. The issue I have is that the formula carries down past the total. The correct total should be 40, but I'm getting total for every row based on the criteria.
The formula I'm using is =IF(A10:A15="--",SUM(B10:B15),100). Unfortunately, my company won't allow me to upload a picture, so I've done my best to recreate what it looks like on the SS.
Any help would be greatly appreciated.
This occurs as you are referencing an array in the IF condition. I assume you enter the formula in B16.
Excel compares each cell in A10:A15 to the "--" condition and returns either the sum of B10:B15 if true or 100 if false. The result is spilled into cells B16:B21.
This is what happens:
A10 is NOT equal to "--" so B16 becomes 100
A11 is NOT equal to "--" so B17 becomes 100 also
A12 is equal to "--" so B18 returns the sum of B10:B15 being 40
and so on.
So, this explains what you are experiencing, but I'm not clear on what result you want to achieve.
Perhaps =SUMIF(A10:A15,"<>--",B10:B15) in B16 is what you need as this returns 40. Probably not, as it doesn't take the 100 into account. Or is it perhaps that you want to return 100 is all cells in A10:15 equal "--" and otherwise the sum of the cells that are not equal to "--"? Then this would work:
=IF(SUM(--(A10:A15<>"--")),SUMIF(A10:A15,"<>--",B10:B15),100)
So please clarify.
