Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining subtotal and sumif functions
I would like to combine the sumif function with filters and sum only the data
chosen in the filter. Subtotal (109,xxx) would ignore the hidden data but I am having trouble combining the functions sumif & subtotal. Help much appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining subtotal and sumif functions
Try...
=SUMPRODUCT(--(CondRange="Cond"),SUBTOTAL(109,OFFSET(RangeToSum, ROW(Range ToSum)-MIN(ROW(RangeToSum)),0,1))) Note that if the condition is a numerical value, remove the quotes. Hope this helps! In article , TPDigg wrote: I would like to combine the sumif function with filters and sum only the data chosen in the filter. Subtotal (109,xxx) would ignore the hidden data but I am having trouble combining the functions sumif & subtotal. Help much appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining subtotal and sumif functions
Domenic,
thanks for the attempt but that seems to be returning a 0 when there should be data. I would have thought something like this would work but it doesn't - =sumif(range,criteria,subtotal(109,range)). I am far from an expert, do you see why this isn't working? Thanks "Domenic" wrote: Try... =SUMPRODUCT(--(CondRange="Cond"),SUBTOTAL(109,OFFSET(RangeToSum, ROW(Range ToSum)-MIN(ROW(RangeToSum)),0,1))) Note that if the condition is a numerical value, remove the quotes. Hope this helps! In article , TPDigg wrote: I would like to combine the sumif function with filters and sum only the data chosen in the filter. Subtotal (109,xxx) would ignore the hidden data but I am having trouble combining the functions sumif & subtotal. Help much appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining subtotal and sumif functions
Domenic,
Perfect!!! I got it to work. Thank you so much for the help. You saved me days of work. "Domenic" wrote: Try... =SUMPRODUCT(--(CondRange="Cond"),SUBTOTAL(109,OFFSET(RangeToSum, ROW(Range ToSum)-MIN(ROW(RangeToSum)),0,1))) Note that if the condition is a numerical value, remove the quotes. Hope this helps! In article , TPDigg wrote: I would like to combine the sumif function with filters and sum only the data chosen in the filter. Subtotal (109,xxx) would ignore the hidden data but I am having trouble combining the functions sumif & subtotal. Help much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter with Subtotal Sumif | Excel Worksheet Functions | |||
subtotal and sumif | Excel Worksheet Functions | |||
Subtotal on SumIf | Excel Worksheet Functions | |||
Combining SUMIF and SUBTOTAL functions | Excel Worksheet Functions | |||
Can you combined the SUMIF and SUBTOTAL functions in a formula? | Excel Worksheet Functions |