Replacement for SUMIFS

AlexaS

Board Regular
Joined
Oct 12, 2012
Messages
158
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

1
10.00
2
A
20.00
2
A
25.00
4
0.00
4
D
40.00
4
D
5.00

<TBODY>
</TBODY>


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.

Code:
=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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi Alex,

Maybe...

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

Adjust the ranges

M.
 
Upvote 0
Hi Alex,

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

Excel Workbook
ABCDEFGH
1Data1Data2Data3Data1Data2Result
21104D40
32A202A20
42A25
540
64D40
74D5
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
 
Upvote 0
Thank you Marcelo and Akashwani - the index and match functions were just what I needed.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top