# Replacement for SUMIFS

#### AlexaS

##### Board Regular
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 2 A 20 2 A 25 4 0 4 D 40 4 D 5

<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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi Alex,

Maybe...

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

M.

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

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

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

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

You are welcome and thanks for the feedback

M.

Hi Alex,

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

Ak

Replies
18
Views
255
Replies
3
Views
72
Replies
3
Views
1K
Replies
4
Views
221
Replies
6
Views
134

1,196,288
Messages
6,014,500
Members
441,825
Latest member

### 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.

### Which adblocker are you using?

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

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