How to Sum on Multiple Criterion

[Don R]

Greetings,
I have a question about how to sum a list of values with more than
one criterion.

Here's the source range located on sheet 1.
A B C D E
1 Pay# Name Charge# Hrs
2 2140 D. Rist AE014.19 4
3 1730 M. Jones AE014.19 6
4 2140 D. Rist AE014.13 12
5 2140 D. Rist AE014.19 5

Here's the result I'm trying to get on sheet 2. The sum of all hours
where pay# =2140 and Charge# = AE014.13, then the sum of all hours where
pay#=2140 and Charge#=AE014.19. Then the sum of all hours where
pay#=1730 and Charge#=AE014.13 and so on.

A B C D E
Pay# Name AE014.13 AE014.19
1 2140 D. Rist 12 9
2 1730 M. Jones 6

What the formula would do is look at the Pay# in Col B and the Charge# in
Row1, then use those two criterion to get the sum of all hours on sheet 1
where the Pay# and Charge# match.

In essence, what I need to do is sum a list of values based on two
criterion the look up the results based on the same two criterion. I'd
appreciate any help, and would especially like to see the sample equation
needed to do the job.

Thanks,
Don Rist

 

[Ragdyer]

Try this in D2 of Sheet2:

=SUMPRODUCT(($B2=Sheet1!$B$2:$B$20)*(D$1=Sheet1!$D$2:$D$20)*Sheet1!$E$2:$E$2
0)

Drag across to E2 to copy it.

Then select *both* D2 & E2, and drag down to copy as needed.

 

[Don Guillett]

Try
=sumproduct((a2:a22="x")*(b2:b22=1)) to count

=sumproduct((a2:a22="x")*(b2:b22=1)*c2:c22)to sum c based on a & b

 

[Macgru]

Greetings,
I have a question about how to sum a list of values with more than
one criterion.

Here's the source range located on sheet 1.
A B C D E
1 Pay# Name Charge# Hrs
2 2140 D. Rist AE014.19 4
3 1730 M. Jones AE014.19 6
4 2140 D. Rist AE014.13 12
5 2140 D. Rist AE014.19 5

Here's the result I'm trying to get on sheet 2. The sum of all hours
where pay# =2140 and Charge# = AE014.13, then the sum of all hours where
pay#=2140 and Charge#=AE014.19. Then the sum of all hours where
pay#=1730 and Charge#=AE014.13 and so on.

A B C D E
Pay# Name AE014.13 AE014.19
1 2140 D. Rist 12 9
2 1730 M. Jones 6

What the formula would do is look at the Pay# in Col B and the Charge# in
Row1, then use those two criterion to get the sum of all hours on sheet 1
where the Pay# and Charge# match.

In essence, what I need to do is sum a list of values based on two
criterion the look up the results based on the same two criterion. I'd
appreciate any help, and would especially like to see the sample equation
needed to do the job.

Thanks,
Don Rist

try
=sumproduct((b2:b5=2140)*(d2:d5=AE014.13)) as array formula to sum 1st 2
criterias
and so on for the rest
to have array formula you have to press ctrl+shift+enter
mcg

 

[Ron Rosenfeld]

Greetings,
I have a question about how to sum a list of values with more than
one criterion.

Here's the source range located on sheet 1.
A B C D E
1 Pay# Name Charge# Hrs
2 2140 D. Rist AE014.19 4
3 1730 M. Jones AE014.19 6
4 2140 D. Rist AE014.13 12
5 2140 D. Rist AE014.19 5

Here's the result I'm trying to get on sheet 2. The sum of all hours
where pay# =2140 and Charge# = AE014.13, then the sum of all hours where
pay#=2140 and Charge#=AE014.19. Then the sum of all hours where
pay#=1730 and Charge#=AE014.13 and so on.

A B C D E
Pay# Name AE014.13 AE014.19
1 2140 D. Rist 12 9
2 1730 M. Jones 6

What the formula would do is look at the Pay# in Col B and the Charge# in
Row1, then use those two criterion to get the sum of all hours on sheet 1
where the Pay# and Charge# match.

In essence, what I need to do is sum a list of values based on two
criterion the look up the results based on the same two criterion. I'd
appreciate any help, and would especially like to see the sample equation
needed to do the job.

Thanks,
Don Rist

The "simple" formula is:

=SUMPRODUCT((Pay=2140)*(Charge="ae014.19")*Hrs)

The Named Ranges should obvious. Cell references can be substituted for the
constants.

However, I would recommend you explore the use of a Pivot Table for this
report. With a selection within the table, select Data/Pivot Table and
Chart/Next/Next/Finish.

Then drag Pay# to the rows area; charge# to the columns (or vice versa); and
hrs to the data section. Format it as you wish (see Format/AutoFormat).



Hrs Charge#
Pay# AE014.13 AE014.19 Grand Total
1730 6 6
2140 12 9 21
Grand Total 12 15 27


--ron

 

[Ragdyer]

I'm sending this for the third time!

Any problems with the servers?Try this in D2 of Sheet2:

=SUMPRODUCT(($B2=Sheet1!$B$2:$B$20)*(D$1=Sheet1!$D$2:$D$20)*Sheet1!$E$2:$E$2
0)

Drag across to E2 to copy it.

Then select *both* D2 & E2, and drag down to copy as needed.

 

[Don R]

Thanks to everyone for the help. The SUMPRODUCT function did the trick.
I found that selecting an entire column did not work. Instead I had to
key in the exact range like B2:B214 instead of B:B. Also, if I included
the header row like B1:B214 instead of B2:B214 the function failed. But
once I got the ranges right, it worked like a champ. Here's a sample
formula for one of the cells on sheet 2 named Oct01-04.

=SUMPRODUCT(('FY-05Hrs'!$B$2:$B$214='Oct01-04'!$A11)*('FY-05Hrs'!$E$2:$E$214='Oct01-04'!M$10)*'FY-05Hrs'!$D$2:$D$214)