Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUBTOTAL in SUMIF formula
Greetings folks. I am trying to use SUMIF with a nested SUBTOTAL function
like so =SUMIF(C10:C50000,C2,SUBTOTAL(9,D10:D50000)). I only want the sum_range to sum based on a filter, and thus the SUBTOTAL. I know this is not possible the way I have it , because it is not working, but is it possible some other way? If not, does anyone have a suggstion? Thank you Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUBTOTAL in SUMIF formula
What's in your Range C10:C50000 and also C2?
"Greg Snidow" wrote: Greetings folks. I am trying to use SUMIF with a nested SUBTOTAL function like so =SUMIF(C10:C50000,C2,SUBTOTAL(9,D10:D50000)). I only want the sum_range to sum based on a filter, and thus the SUBTOTAL. I know this is not possible the way I have it , because it is not working, but is it possible some other way? If not, does anyone have a suggstion? Thank you Greg |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUBTOTAL in SUMIF formula
What is in your C2 and your C10:C50000 (what type of data)?
"Greg Snidow" wrote: Greetings folks. I am trying to use SUMIF with a nested SUBTOTAL function like so =SUMIF(C10:C50000,C2,SUBTOTAL(9,D10:D50000)). I only want the sum_range to sum based on a filter, and thus the SUBTOTAL. I know this is not possible the way I have it , because it is not working, but is it possible some other way? If not, does anyone have a suggstion? Thank you Greg |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUBTOTAL in SUMIF formula
C2:C5 Contain the sum_criteria, text data. C10:C50000 has all cells filled
with one of the values from C2:C5. "Jim May" wrote: What is in your C2 and your C10:C50000 (what type of data)? "Greg Snidow" wrote: Greetings folks. I am trying to use SUMIF with a nested SUBTOTAL function like so =SUMIF(C10:C50000,C2,SUBTOTAL(9,D10:D50000)). I only want the sum_range to sum based on a filter, and thus the SUBTOTAL. I know this is not possible the way I have it , because it is not working, but is it possible some other way? If not, does anyone have a suggstion? Thank you Greg |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUBTOTAL in SUMIF formula
the Sumproduct() function will likely solve your problem. see it in help or
http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Greg Snidow" wrote: C2:C5 Contain the sum_criteria, text data. C10:C50000 has all cells filled with one of the values from C2:C5. "Jim May" wrote: What is in your C2 and your C10:C50000 (what type of data)? "Greg Snidow" wrote: Greetings folks. I am trying to use SUMIF with a nested SUBTOTAL function like so =SUMIF(C10:C50000,C2,SUBTOTAL(9,D10:D50000)). I only want the sum_range to sum based on a filter, and thus the SUBTOTAL. I know this is not possible the way I have it , because it is not working, but is it possible some other way? If not, does anyone have a suggstion? Thank you Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUBTOTAL in SUMIF formula
Jim, thanks for responding. I don't understand how SUMPRODUCT will help. If
I have in C2:C5, "Blue","Orange","Green","Brown","Slate" and in C10:C50000 all cells are filled with one of these five values, how will SUMPRODUCT help? What I should have mentioned is that column A is populated with months in '2007_10' format. I am able to get the SUMIF to work using C2:C5 as criteria, but what I want to happen is that value to also only include values within the range of data I have filtered in column A. So if I have column A filtered to '2007_10', I only want to see values from the sum range for October. Does that make sense? Greg "Jim May" wrote: the Sumproduct() function will likely solve your problem. see it in help or http://www.xldynamic.com/source/xld.SUMPRODUCT.html "Greg Snidow" wrote: C2:C5 Contain the sum_criteria, text data. C10:C50000 has all cells filled with one of the values from C2:C5. "Jim May" wrote: What is in your C2 and your C10:C50000 (what type of data)? "Greg Snidow" wrote: Greetings folks. I am trying to use SUMIF with a nested SUBTOTAL function like so =SUMIF(C10:C50000,C2,SUBTOTAL(9,D10:D50000)). I only want the sum_range to sum based on a filter, and thus the SUBTOTAL. I know this is not possible the way I have it , because it is not working, but is it possible some other way? If not, does anyone have a suggstion? Thank you Greg |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUBTOTAL in SUMIF formula
You can use the CSE formula
sum(if ($c$10:$c$50000=$C2,$D$10:$D$50000)) You must press CTRL+Shift+Enter together to enter this in the cell put it in D2 and then copy it to D3,D4 and D5. You have to copy it one cell at a time. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUBTOTAL in SUMIF formula
nest it to make your date citeria work.
sum(if ($c$10:$c$50000=$C2,if($a$10:$a$50000=$A2,$D$10:$D $50000))) put the date you want to filter for in A2:A5 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using sumif with subtotal | Excel Discussion (Misc queries) | |||
Subtotal and sumif help | Excel Discussion (Misc queries) | |||
Sumif & subtotal | Excel Discussion (Misc queries) | |||
Subtotal on SumIf | Excel Worksheet Functions | |||
Can you combined the SUMIF and SUBTOTAL functions in a formula? | Excel Worksheet Functions |