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:
<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
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:
1 | A | B | C | D | E | F | G |
2 | JAN. | Category 1 | 1999 Data | 2000 Data | 2001 Data | 2002 Data | 2003 Data |
3 | JAN. | Category 2 | "" | "" | "" | "" | "" |
4 | JAN. | Exp. X | $500 | $100 | $300 | $200 | $400 |
5 | JAN. | Exp. Losses | ($50) | $0 | ($100) | $0 | ($200) |
6 | JAN. | Category 5 | "" | "" | "" | "" | "" |
7 | JAN. | Category 6 | "" | "" | "" | "" | "" |
8 | JAN. | Category 7 | "" | "" | "" | "" | "" |
9 | FEB. | Category 1 | "" | "" | "" | "" | "" |
10 | FEB. | Category 2 | "" | "" | "" | "" | "" |
11 | FEB. | Exp. X | $200 | $300 | $100 | $200 | $500 |
12 | FEB. | Exp. Losses | $0 | $0 | ($25) | $0 | $0 |
13 | FEB. | Category 5 | "" | "" | "" | "" | "" |
14 | FEB. | Category 6 | "" | "" | "" | "" | "" |
15 | FEB. | 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