# Thread: SUMIF & Concatenate (Excel 2002)

1. ## SUMIF & Concatenate (Excel 2002)

Hi,
I'm in need of a formula that will look at a large database and then sum the dollar amount base on 3 different variables. For example, I'd like to sum the dollar amounts if it is company "84", nature "Sales" and month "JAN07". I'm not wanting to use a pivot table (for various reasons) and currently I add another column in the database to concatenate the 3 variables and then do a sumif to add. Is there an easier formula... maybe the "match" formula?? I can't seem to get that one to work?!!!? Any ideas will be greatly appreciated. I've attached a worksheet with an example.
Thanks!
Lana

2. ## Re: SUMIF & Concatenate (Excel 2002)

For "SUMIF" formulas with multiple conditions, you can use SUMPRODUCT:
<code>
=SUMPRODUCT((A2:A7=84)*(B2:B7="Sales")*(C2:C7="JAN 07")*D27)
</code>
or if you put the values you want to filter on in (for example) L1, L2 and L3:
<code>
=SUMPRODUCT((A2:A7=L1)*(B2:B7=L2)*(C2:C7=L3)*D27)
</code>
Alternatively, you can use this array formula (confirm with Ctrl+Shift+Enter):
<code>
=SUM(IF((A2:A7=L1)*(B2:B7=L2)*(C2:C7=L3),D27))
</code>
The latter will also work with other aggregate functions such as MIN, MAX and AVERAGE.

3. ## Re: SUMIF & Concatenate (Excel 2002)

Awesome...thanks Hans!
Lana

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•