Subtotal and Sumif combination help

[Sourkraut]

Alright, I need help combining two formulas.

What we need is to subtotal a range based on a criteria and we need that subtotal to change when the range is filtered.

We have two formulas that work seperately but we need to combine them into one.

=SUBTOTAL(9,Summary!H3:H39)

=SUMIF(Summary!F3:F39,B35,Summary!H3:H39)

B35 = The name we're trying reference
Column F is the column that would hold that name
Column H has the values we want to sum

 

[Sachin2k6]

thanks guys

 

[Murugesan_MD]

Hi Barry.. Can u Please explain this formula..?

 

[Aladin Akyurek]

Hi Barry.. Can u Please explain this formula..?

http://www.mrexcel.com/forum/excel-questions/701109-count-if-filters-2.html

contains an explanation, albeit somewhat terse. (Substitute Sum for Counta.)

 

[tnethercott]

Alright, I need help combining two formulas.

What we need is to subtotal a range based on a criteria and we need that subtotal to change when the range is filtered.

We have two formulas that work seperately but we need to combine them into one.

=SUBTOTAL(9,Summary!H3:H39)

=SUMIF(Summary!F3:F39,B35,Summary!H3:H39)

B35 = The name we're trying reference
Column F is the column that would hold that name
Column H has the values we want to sum

I am trying to do something similar. I was using the formula provided by barry houdini to get a subtotal with the sumif.

However, now I need to do the same but only for transactions that are past due (criteria would be before / less than a specified date, today() )

Here is what I need combined:

=SUBTOTAL(9,J11:J500)

=SUMIF(K11:K241,"<"&N2,J11:J241)
the above could also be written as =SUMIF(K11:K241,"<"&today(),J11:J241)

Thank you!

 

[Aladin Akyurek]

I am trying to do something similar. I was using the formula provided by barry houdini to get a subtotal with the sumif.

However, now I need to do the same but only for transactions that are past due (criteria would be before / less than a specified date, today() )

Here is what I need combined:

=SUBTOTAL(9,J11:J500)

=SUMIF(K11:K241,"<"&N2,J11:J241)
the above could also be written as =SUMIF(K11:K241,"<"&today(),J11:J241)

Thank you!

Are you after this?

=SUMPRODUCT(SUBTOTAL(9,OFFSET(J11,ROW(J11:J241)-ROW(J11),0,1)),--(K11:K241 < TODAY()))

 

[tnethercott]

Are you after this?

=SUMPRODUCT(SUBTOTAL(9,OFFSET(J11,ROW(J11:J241)-ROW(J11),0,1)),--(K11:K241 < TODAY()))

YES! That did it, thank you so much!

 

[Aladin Akyurek]

YES! That did it, thank you so much!

You are welcome. Thanks for providing feedback.

 

[Number4]

Sorry I got the ranges the wrong way round, this should fix it.....

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Summary!H3,ROW(Summary!H3:H39)-ROW(Summary!H3),0)),(Summary!F3:F39=B35)+0)

Awesome! Wish I understood it better though. I read the descriptions off all the codes, just got lost. But this did work.
If it's this "easy" to create a formula that does it, why can't it just be found under 'Subtotal?'

 

[Aladin Akyurek]

Awesome! Wish I understood it better though. I read the descriptions off all the codes, just got lost. But this did work.
If it's this "easy" to create a formula that does it, why can't it just be found under 'Subtotal?'

Have a look at:

http://www.mrexcel.com/forum/excel-questions/701109-count-if-filters.html#post3463964

though.

 

[ianlnai]

hi..i need help with this formula :

[/URL][/IMG]

i need to combine these two formula :
- SUMIF($B$2:$B$17,$B21,$C$2:$C$17)
- SUBTOTAL(9,$C$2:$C$17)

thx before