Sum a range based on two variables
 

I would like to sum a range if the value is over a certain amount.

dave 10
dave 20
dave 30
paul 20
paul 30

If I only want greater than 20 I would like to get
Dave 50
Paul 50

I used a sumif to get the total for the column -
=SUMIF($B$3:$B$182,$B222,D$3:D$182) where b is name and d is values, so I
just want to sum d information if greater than some value. Any help out
there. Thanks.

Sum a range based on two variables
 

=SUMPRODUCT((C1:C22="dave")*(D1:D22=20)*D1:D22)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"billy" wrote in message
...
I would like to sum a range if the value is over a certain amount.

dave 10
dave 20
dave 30
paul 20
paul 30

If I only want greater than 20 I would like to get
Dave 50
Paul 50

I used a sumif to get the total for the column -
=SUMIF($B$3:$B$182,$B222,D$3:D$182) where b is name and d is values, so I
just want to sum d information if greater than some value. Any help out
there. Thanks.

Sum a range based on two variables
 

Try this:

=SUMPRODUCT(--($B$3:$B$182=$B222),--(D$3:D$18220),D$3:D$182)

--
Biff
Microsoft Excel MVP


"billy" wrote in message
...
I would like to sum a range if the value is over a certain amount.

dave 10
dave 20
dave 30
paul 20
paul 30

If I only want greater than 20 I would like to get
Dave 50
Paul 50

I used a sumif to get the total for the column -
=SUMIF($B$3:$B$182,$B222,D$3:D$182) where b is name and d is values, so I
just want to sum d information if greater than some value. Any help out
there. Thanks.