SUMIFS Using 3 Distinct Criteria

HGLIII

New Member
Joined
Jun 19, 2019
Messages
14
Good Morning,

I was hoping someone might be able to help me out with the following question:

I have built a spreadsheet that generates data on a monthly basis for 100 distinct "blocks" (January through December, vertically oriented, 100 times; the example below features only 2 months for 1 of those 100 12-month "blocks"), with each month containing several identical categories (two of which are "Exp. X" and "Exp. Losses").

Column B features all of the listed categories and Columns C through V represent unique situations based on years, as such:

1ABCDEFG
2JAN.Category 11999 Data2000 Data2001 Data2002 Data2003 Data
3JAN.Category 2""""""""""
4JAN.Exp. X$500$100$300$200$400
5JAN.Exp. Losses($50)$0($100)$0($200)
6JAN.Category 5""""""""""
7JAN.Category 6""""""""""
8JAN.Category 7""""""""""
9FEB.Category 1
""""""""""
10FEB.Category 2
""""""""""
11FEB.Exp. X
$200$300$100$200$500
12FEB.Exp. Losses
$0$0($25)$0$0
13FEB.Category 5
""""""""""
14FEB.Category 6
""""""""""
15FEB.Category 7
"""""'""""

<tbody>
</tbody>

Would it be possible to SUM all of the Exp. X numbers in Column C (which would encompass both the January and February results of course), but only those that also feature a loss in Exp. Losses?

Meaning, something along the lines of SUMIFS(B1:B15,"*Exp. X*",C1:C15) but then only if AND(B1:B15,"*Exp. Losses*",C1:C15,"<0"). I am trying to pair the SUM of the Exp. X with only the cells that also feature a negative number in the Exp. Losses cell.

The actual results for Column C would equal $500, because the $200 Exp. X in the month of February does not feature a loss in the Exp. Loss category.

Any help with crafting the formula would be greatly appreciated!

Many thanks,

HGL
 

dms37

Board Regular
Joined
Nov 29, 2006
Messages
231
My real answer is you'd be better with a helper column you just sum but if it needs to be a formula in one cell this works when confirmed with Ctrl+Shift+Enter

=SUM(IF(B1:B14=$B$3,IF(B2:B15=$B$4,IF(C2:C15<0,C1:C14,0),0),0))
 

HGLIII

New Member
Joined
Jun 19, 2019
Messages
14
Thanks very much for the reply - if you have a second, would you mind explaining the rationale behind the formula? For example, what is the purpose of using B1:B14 instead of B15 to encompass the entire range, as well as then using B2/C2:B15/C15 followed by C1:C14? Additionally, why does B1:B14 = $B$3 instead of $B$4, which is the targeted reference cell?

I am conveying this formula to a pre-existing sheet, so understanding the logic behind it would be helpful if you don't mind explaining.

Thanks!
 

dms37

Board Regular
Joined
Nov 29, 2006
Messages
231
There are only 14 rows, I ignored #1 being the line with A, B, C etc in
 

HGLIII

New Member
Joined
Jun 19, 2019
Messages
14
Understood, thank you - for some reason the formula isn't working for me.

I made sure to create an array formula with CTRL+SHFT+ENTER, but still no luck.

Do you think it would be easier to explain the logic behind a nested IF statement (I suppose that is what we are doing with this)?

To reiterate, just trying to create a formula that encompasses the following: SUM all of the cells in Column C that correspond to the cells in Column B titled "Exp. X", if and only if the cells in Column C that correspond with the cells in Column B titled "Exp. Losses*" are less than $0 (negative).
 

dms37

Board Regular
Joined
Nov 29, 2006
Messages
231
The nested ifs are because an array formula won’t allow an AND function in the IF. So they’re just if the first criteria is met, move on to the second and if that is met then check the third. If all are met return the corresponding result from the range otherwise return zero and then sum all the answers.

It definitely works as I tried it first before posting, only thing that I had to tweak is the $ signs in the money made my Excel think they were text but that’s just because I copied it from your post.
 

HGLIII

New Member
Joined
Jun 19, 2019
Messages
14
Excellent explanation, thanks dms - here is my formula:

{=SUM(IF($B$2:$B$16901="*Exp. X*",IF($B$2:$B$16901="*Exp. Losses*",IF(C2:C16901<0,C2:C16901,0),0),0))}

Any thoughts as to what I might be doing wrong here? I think there might be something going on with the textual component ("*Exp. X*" and "*Exp. Losses*") - perhaps you can't use "=" followed by the "* *"?.
 

dms37

Board Regular
Joined
Nov 29, 2006
Messages
231
The ranges for checking exp losses and the <0 need to be 1 row lower as the data is underneath the exp x and the vales being summed. The middle 2 ranges in the formula need to be from rows 3 to 16902
 

Forum statistics

Threads
1,078,235
Messages
5,339,003
Members
399,274
Latest member
WilliamWavehill

Some videos you may like

This Week's Hot Topics

Top