Replacement for SUMIFS

[AlexaS]

Hello,

I've been searching for possible solutions to this particular problem for an hour (seems like every time I solve one thing, I break five others).

What I'm trying to do:

I need to search a report based on two values (for an example lets say 4 and D). They are in two separate columns on the report page. I need to return the value in a third column based on these two criterias. I need to return only the first value in the third column.

For Example:

This is the report

[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]1
[/TD]
[TD][/TD]
[TD]10.00
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]20.00
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A
[/TD]
[TD]25.00
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD]0.00
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]D
[/TD]
[TD]40.00
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]D
[/TD]
[TD]5.00
[/TD]
[/TR]
</TBODY>[/TABLE]


So I need to find the first 4 & D combo and only return the 40.00 that is in the third column. Currently I have a SUMIFS as the formula.

=SUMIFS(TB!$F:$F,TB!$A:$A,'Source '!B5,TB!$B:$B,'Source '!C5)

But this pulls back 40.00 + 5.00 and returns 45.00. Is there another formula that will allow me to search by both criteria but return the first matching value?

Thank you,

-Alex

 

[Marcelo Branco]

Hi Alex,

Maybe...

=INDEX(C:C,MATCH(1,INDEX((A1:A100=4)*(B1:B100="D"),0),0))

Adjust the ranges

M.

 

[Akashwani]

Hi Alex,

Probably not the best solution, but does this work for you?...

Excel Workbook
	A	B	C	D	E	F	G	H
1	Data1	Data2	Data3		Data1	Data2	Result
2	1		10		4	D	40
3	2	A	20		2	A	20
4	2	A	25
5	4		0
6	4	D	40
7	4	D	5
8
Sheet9

The formula in G2 needs to be entered with ctrl shift enter NOT just enter.
You will need to change your cell references to suit your layout.

I hope that helps.

Ak

 

[AlexaS]

Thank you Marcelo and Akashwani - the index and match functions were just what I needed.

 

[jamtay317]

this is asumming that these columns are in A B C and that you can put your criteia in D1 and E1
=sumproduct(--($A$1:$A$100=$D1)*($B$1:$B$100=$E$1),($C$1:$C$100) and you have to hit CTRL +SFT+ ENTER

 

[Marcelo Branco]

Thank you Marcelo and Akashwani - the index and match functions were just what I needed.

You are welcome and thanks for the feedback

M.

 

[Akashwani]

Hi Alex,

I'm pleased you got a solution and thanks for the feedback.

Ak