MrExcel.com - Your One Stop for Excel Tips & Solutions

Sumif with Multiple Criteria

Posted by Debbie on August 01, 2000 4:44 AM

Will Sumif work with 2 criteria? I have 2 worksheets, the 1st worksheet is a database by tax type for each location and the 2nd worksheet is a journal entry. Basically what I want the formula (on worksheet 2) to do is if:

Worksheet1 A:A = Worksheet2 B1 AND Worksheet1 B:B = Worksheet2 C1, then sum the numbers.

FYI-Column 1 is tax type, (FICA, STATE, etc.) and Column 2 is location. I like to avoid a VB programming situation but I might have to resort to it yet to create this massive journal entry.

Posted by Celia on August 01, 0100 5:42 AM

Debbie
A formula can do it - but what is it that you want to sum?
Celia

Posted by Debbie on August 01, 0100 5:48 AM

Posted by Debbie on August 01, 0100 5:57 AM

I want to pick up the number in D1. The 1st worksheet is coming from our payroll register and is set up like this:

Tax Name Location Amount
FICA 2 10.00
FICA 5 25.00
LIFE 2 2.50
LIFE 5 7.00

What I am trying to do is pick up in a preset report, FICA, Location 2, 10.00. I have used the SUMIF formula to do this when I have 1 criteria, but I have never used multilple criteria to locate the info in column 3. Even in a lookup situation, it would have to look at 2 criteria to chose the right number.

Any suggestions would be helpful as this is a very time consuming entry for a biweekly payroll.

Thanks!

Posted by Celia on August 01, 0100 6:14 AM


Debbie
I'm not sure if this is what you want but if not, it should give you an idea of the formula structure for what you need to do.
Post again if it doesn't.

=SUM(IF((Sheet1!$A$1:$A$10000=B1)*(Sheet1!$B$1:$B$10000=C1),1,0)*D1)

Celia
=SUM(IF((Sheet1!$A$1:$A$10000=B1)*(Sheet1!$B$1:$B$10000=C1),1,0)*D1)

Posted by Debbie on August 01, 0100 6:32 AM

Bummer, it didn't work. What the the '*' do in the formula? You're still great in my book.

Posted by Celia on August 01, 0100 7:28 AM

Debbie
The "*" basically means "and".
The formula is an array formula. Did you press Ctrl+Shift+Enter?
Celia

Posted by Gary on August 03, 0100 8:22 AM

This is exactly what I'm trying to do also.
I tried it and it didn't work for me either.
Are we missing something subtle in the syntax?

Gary

Posted by Celia on August 03, 0100 4:52 PM


Gary
Unfortunately, I don't understand what it is you're trying to do.
If you wish, you can send me a sample workbook.
Celia