Sumif with multiple criteria

[wrightlefty]

I have a resource table that is broken down by month and by project, but some of the projects are grouped together later for reporting purposes. I want a formula that will check 2 condition columns, and then sum the corresponding data from a third column.

In this example, Projects A and C are both part of Group A:

Proj A
jan 3 Group A
feb 5 Group A
mar 4 Group A
Proj B
jan 2 Group B
feb 3 Group B
mar 1 Group B
Proj C
jan 2 Group A
feb 4 Group A
mar 6 Group A



Group A Group B
jan 5 2
feb 9 3
mar 10 1

I can't make it work with sumif, and I've seen a lot about sumproduct, but I can't figure out how to make that work here, either. Please help.

 

[SkipVought]

Hi,

If I am reading your post correctly, you are one of the poster childern for thread68-1525860.

Bit your worksheet can be solved with SUMPRODUCT.

My results top-left in E1
[tt]
Group A Group B
jan 5 2
feb 9 3
mar 10 1
[/tt]
My formula
[tt]
F2: =SUMPRODUCT(($C$2:$C$12=F$1)*($A$2:$A$12=$E2)*($B$2:$B$12))
[/tt]
My source table top-left in A1



Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, with a properly designed TABLE, a PivotTable solution could have been achieved in about 15 seconds, without any formulas.
[tt]
Project Mon Amount Group
Proj A Jan-08 3 Group A
Proj A Feb-08 5 Group A
Proj A Mar-08 4 Group A
Proj B Jan-08 2 Group B
Proj B Feb-08 3 Group B
Proj B Mar-08 1 Group B
Proj C Jan-08 2 Group A
Proj C Feb-08 4 Group A
Proj C Mar-08 6 Group A

[/tt]
PivotTable Report
[tt]
Sum of Amount Group
Mon Group A Group B Grand Total
Jan-08 5 2 7
Feb-08 9 3 12
Mar-08 10 1 11
Grand Total 24 6 30
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[wrightlefty]

That worked great. Thank you! I agree, it's time for me to learn PivotTables.