for SUMIF function, how do I use 2 sets of range & criteria
 

I am trying to sum one column set if it meets the conditions of two other
column sets
Example
Site Level Work Effort
A 1 .2
A 2 .1
A 2 .15
B 2 .6
C 3 .1
In this example I want to sum Work effort for all Level 2s that reside in
Site A. Basically it requires two sets of Range/Criteria in the Sum if
function. Is this possible? I am have trouble trying to include it in the
formula

You can use a sumproduct function:

=SUMPRODUCT(--($A$2:$A$100="A"),--($B$2:$B$100=1),$C$2:$C$100)

and of course you can change the "A" and 1 to reference cells containing
those values.

Hope this helps
Rowan

Bob wrote:
I am trying to sum one column set if it meets the conditions of two other
column sets
Example
Site Level Work Effort
A 1 .2
A 2 .1
A 2 .15
B 2 .6
C 3 .1
In this example I want to sum Work effort for all Level 2s that reside in
Site A. Basically it requires two sets of Range/Criteria in the Sum if
function. Is this possible? I am have trouble trying to include it in the
formula

Hi!

A1 = A
B1 = 2

=SUMPRODUCT(--(A5:A50=A1),--(B5:B50=B1),C5:C50)

Biff

"Bob" wrote in message
...
I am trying to sum one column set if it meets the conditions of two other
column sets
Example
Site Level Work Effort
A 1 .2
A 2 .1
A 2 .15
B 2 .6
C 3 .1
In this example I want to sum Work effort for all Level 2s that reside in
Site A. Basically it requires two sets of Range/Criteria in the Sum if
function. Is this possible? I am have trouble trying to include it in the
formula

Excellent, I will give it a try.
Thanks
Bob

"Biff" wrote:

Hi!

A1 = A
B1 = 2

=SUMPRODUCT(--(A5:A50=A1),--(B5:B50=B1),C5:C50)

Biff

"Bob" wrote in message
...
I am trying to sum one column set if it meets the conditions of two other
column sets
Example
Site Level Work Effort
A 1 .2
A 2 .1
A 2 .15
B 2 .6
C 3 .1
In this example I want to sum Work effort for all Level 2s that reside in
Site A. Basically it requires two sets of Range/Criteria in the Sum if
function. Is this possible? I am have trouble trying to include it in the
formula

Excellent, I will give it a try.
Thanks
Bob


"Rowan Drummond" wrote:

You can use a sumproduct function:

=SUMPRODUCT(--($A$2:$A$100="A"),--($B$2:$B$100=1),$C$2:$C$100)

and of course you can change the "A" and 1 to reference cells containing
those values.

Hope this helps
Rowan

Bob wrote:
I am trying to sum one column set if it meets the conditions of two other
column sets
Example
Site Level Work Effort
A 1 .2
A 2 .1
A 2 .15
B 2 .6
C 3 .1
In this example I want to sum Work effort for all Level 2s that reside in
Site A. Basically it requires two sets of Range/Criteria in the Sum if
function. Is this possible? I am have trouble trying to include it in the
formula

You're welcome. Thanks for the feedback!

Biff

"Bob" wrote in message
...
Excellent, I will give it a try.
Thanks
Bob

"Biff" wrote:

Hi!

A1 = A
B1 = 2

=SUMPRODUCT(--(A5:A50=A1),--(B5:B50=B1),C5:C50)

Biff

"Bob" wrote in message
...
I am trying to sum one column set if it meets the conditions of two
other
column sets
Example
Site Level Work Effort
A 1 .2
A 2 .1
A 2 .15
B 2 .6
C 3 .1
In this example I want to sum Work effort for all Level 2s that reside
in
Site A. Basically it requires two sets of Range/Criteria in the Sum if
function. Is this possible? I am have trouble trying to include it in
the
formula

for SUMIF function, how do I use 2 sets of range & criteria
 

I just wanted to say thank you. I've been working on a similar project just
as Bob was for about 3 days now and just stumbled upon this community.
within an hour I was able to answer my question with this posting.

THANKS MUCH!
Kalida

"Rowan Drummond" wrote:

You can use a sumproduct function:

=SUMPRODUCT(--($A$2:$A$100="A"),--($B$2:$B$100=1),$C$2:$C$100)

and of course you can change the "A" and 1 to reference cells containing
those values.

Hope this helps
Rowan

Bob wrote:
I am trying to sum one column set if it meets the conditions of two other
column sets
Example
Site Level Work Effort
A 1 .2
A 2 .1
A 2 .15
B 2 .6
C 3 .1
In this example I want to sum Work effort for all Level 2s that reside in
Site A. Basically it requires two sets of Range/Criteria in the Sum if
function. Is this possible? I am have trouble trying to include it in the
formula