SUMIFS Tricky Multiple Criteria

[rlobera]

Hi guys!

Need some help regarding this matter. I have something like this data in a table:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]January[/TD]
[TD]Item A[/TD]
[TD]56756[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]January[/TD]
[TD]Item B[/TD]
[TD]4353[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]February[/TD]
[TD]Item A[/TD]
[TD]235[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]February[/TD]
[TD]Item B[/TD]
[TD]67457[/TD]
[/TR]
[TR]
[TD]BP[/TD]
[TD]March[/TD]
[TD]Item A[/TD]
[TD]4577[/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD]January[/TD]
[TD]Item A[/TD]
[TD]3452[/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD]February[/TD]
[TD]Item A[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD]January[/TD]
[TD]Item B[/TD]
[TD]577[/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD]February[/TD]
[TD]Item B[/TD]
[TD]4677[/TD]
[/TR]
</tbody>[/TABLE]

What I need is to sum the numbers in column D that belongs to BP in column A, and only from the months associated to Expenses in column A. Please note that the months in column B are not formatted as DATE but as TEXT. Column C is not used.

Thanks!

 

[jtakw]

Hi,

Something like this?

Book1
	A	B	C	D	E	F	G	H	I
1	BP	January	Item A	56756			January	February	March
2	BP	January	Item B	4353		BP	61109	67692	4577
3	BP	February	Item A	235
4	BP	February	Item B	67457
5	BP	March	Item A	4577
6	Expenses	January	Item A	3452
7	Expenses	February	Item A	345
8	Expenses	January	Item B	577
9	Expenses	February	Item B	4677
Sheet25

Cell Formulas
Range	Formula
G2	=SUMIFS($D1:$D9,$A1:$A9,$F2,$B1:$B9,G1)

G2 formula copied across.

 

[rlobera]

Hmmm... not quite!

In one cell I need a formula that sum values in column D that belongs to BP but only from months in column B associated with Expenses. In my example, the formula should sum values that match January and February from BP. The answer shall be: 56756 + 4353 + 235 + 67457 = 128801

 

[svendiamond]

Hmmm... not quite!

In one cell I need a formula that sum values in column D that belongs to BP but only from months in column B associated with Expenses. In my example, the formula should sum values that match January and February from BP. The answer shall be: 56756 + 4353 + 235 + 67457 = 128801

Book1
	A	B	C	D	E	F	G	H	I
1	Column A	Column B	Column C	Column D		Month	BP Expenses	Total	Formula
2	BP	January	Item A	56756		January	2	61109	=IF(G2>0,SUMIFS(D:D,A:A,"BP",B:B,F2),0)
3	BP	January	Item B	4353		February	2	67692	=IF(G3>0,SUMIFS(D:D,A:A,"BP",B:B,F3),0)
4	BP	February	Item A	235		March	0	0
5	BP	February	Item B	67457		April	0	0
6	BP	March	Item A	4577		May	0	0
7	Expenses	January	Item A	3452		June	0	0
8	Expenses	February	Item A	345		July	0	0
9	Expenses	January	Item B	577		August	0	0
10	Expenses	February	Item B	4677		September	0	0
11						October	0	0
12						November	0	0
13						December	0	0
14								128801
Sheet1

Cell Formulas
Range	Formula
G2	=COUNTIFS(A:A,"Expenses",B:B,F2)
H2	=IF(G2>0,SUMIFS(D:D,A:A,"BP",B:B,F2),0)

 

[rlobera]

Yeah, but won't help me in this case. Actually, need a more complex formula that will solve this matter in one single cell.

Thank you!

 

[sheetspread]

It looks like the Expenses rows are a separate table, if so you can write the formula as:

Excel 2010
	A	B	C	D
1	Column A	Column B	Column C	Column D
2	BP	January	Item A	56756
3	BP	January	Item B	4353
4	BP	February	Item A	235
5	BP	February	Item B	67457
6	BP	March	Item A	4577
7	Expenses	January	Item A	3452
8	Expenses	February	Item A	345
9	Expenses	January	Item B	577
10	Expenses	February	Item B	4677
11
12				128801
Sheet1

Cell Formulas
Range	Formula
D12	=SUMPRODUCT(--(COUNTIF($B$7:$B$10,$B$2:$B$6)>0),$D$2:$D$6)

The BP condition can be added if there are others you wish to leave out.

 

[rlobera]

Almost! But it's the same table, not a separate one.

 

[sheetspread]

You can't sort the table so all the Expenses rows are at the bottom? Are there others holding months criteria as well?

 

[svendiamond]

Can you use a VBA solution? It would be easy then

 

[rlobera]

Would love to, but not able to do it. Sorry!