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!

 

[rlobera]

I know it would be much easier, but in this case, I'm not allowed to use VBA for security reasons.

I thought about using INDEX and MATCH within the SUMIF or SUMIFS, but I'm burned out and couldn't figure out a solution just yet.

 

[svendiamond]

My solution worked though, do you really need it all to be in one cell? Can you not add a helper page, use my solution, then hide that page?

 

[jtakw]

I can get this done with just 1 helper column, you can hide that column (in my sample, Column E)

Book1
	A	B	C	D	E	F
1	BP	January	Item A	56756	56756	128801
2	BP	January	Item B	4353	4353
3	BP	February	Item A	235	235
4	BP	February	Item B	67457	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
E1	=IF(A1="BP",IF(COUNTIFS(A$1:A$9,"Expenses",B$1:B$9,B1)>0,D1,""),"")
F1	=SUM(E1:E9)

 

[rlobera]

Yes guys, I know that with a helper page or column you are approaching the solution, but it's not what I'm actually looking for.

I'm pretty sure that combining multiple formulas in one cell we would be able to solve it!

 

[markmzz]

Try the Array Formula (use Ctrl+Shift+Enter to enter the formula) below:

=SUM(IF(ISNUMBER(MATCH(IF($A$2:$A$10="BP",$B$2:$B$10,"A"),IF($A$2:$A$10="Expenses",$B$2:$B$10,"B"),0)),$D$2:$D$10))

Markmzz

 

[rlobera]

Try the Array Formula (use Ctrl+Shift+Enter to enter the formula) below:

=SUM(IF(ISNUMBER(MATCH(IF($A$2:$A$10="BP",$B$2:$B$10,"A"),IF($A$2:$A$10="Expenses",$B$2:$B$10,"B"),0)),$D$2:$D$10))

Markmzz

What do you mean with "A" and "B"?

 

[sheetspread]

What do you mean with "A" and "B"?

It's to return different characters that can be distinguished by the search instead of 0 or false which will be the same.

 

[rlobera]

Mark, you are a magician with Excel. It works like a charm.

Thanks man!

Best,
R.

 

[markmzz]

It's to return different characters that can be distinguished by the search instead of 0 or false which will be the same.

Hi Sheetspread,

Thank you to help the user with your explanation.

Markmzz

 

[markmzz]

Mark, you are a magician with Excel. It works like a charm.

Thanks man!

Best,
R.

You are welcome.

Thank you for the feedback and I'm glad to help.

Markmzz