SUBTOTAL in SUMIF formula

G

Greg Snidow

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
 
G

Greg Snidow

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

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
 
M

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.
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top