Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Confused about setting up functions based on variables? | Excel Worksheet Functions | |||
Recalculate based on 3 of 4 variables. | Excel Discussion (Misc queries) | |||
automatically updating graphs based upon conditional variables | Charts and Charting in Excel | |||
Using formulas to source a value based on 2 variables | Excel Worksheet Functions | |||
counting based ona number of variables. | Excel Worksheet Functions |