SUBTOTAL in SUMIF formula

[Greg Snidow]

Greetings folks. I am trying to use SUMIF with a nested SUBTOTAL function
like so

=SUMIF(C10:C50000,C2,SUBTOTAL(9,D1050000)).

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

 

[Jim May]

What's in your Range C10:C50000 and also C2?

 

[Jim May]

What is in your C2 and your C10:C50000 (what type of data)?

 

[Greg Snidow]

C2:C5 Contain the sum_criteria, text data. C10:C50000 has all cells filled
with one of the values from C2:C5.

 

[Jim May]

the Sumproduct() function will likely solve your problem. see it in help or
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Greg Snidow]

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

 

[mrpolecat]

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.

 

[mrpolecat]

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