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

 

[barry houdini]

Try like this

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

 

[Sourkraut]

Didn't work but we're just going to try an implent a work around since we're kinda on a deadline. Thanks tho!

 

[barry houdini]

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)

 

[Sourkraut]

You're the man! Thanks so much

 

[rangu]

HI,

=SUMIFS($F$12:$F$1048576,$A$12:$A$1048576,$E2,$I$12:$I$1048576,C$2)

=SUBTOTAL(9,$F$12:$F$677)

I need an help to COMBINE ABOVE FORMULA IN A SINGLE CELL so please help

 

[T. Valko]

HI,

=SUMIFS($F$12:$F$1048576,$A$12:$A$1048576,$E2,$I$12:$I$1048576,C$2)

=SUBTOTAL(9,$F$12:$F$677)

I need an help to COMBINE ABOVE FORMULA IN A SINGLE CELL so please help

Kind of a belated reply...

Like this:

=SUMPRODUCT(--($A$12:$A$677=$E2),--($I$12:$I$677=C$2),SUBTOTAL(9,OFFSET($F$12,ROW($F$12:$F$677)-ROW(F$12),0)))

 

[Sachin2k6]

What is the meaning of '--' sign. I think it is to multiply the result with -1 but why???

 

[VoG]

For example

$A$12:$A$677=$E2

gives an array of False/True. The -- coerces that into an array of 0/1 that SUMPRODUCT can process.

 

[Aladin Akyurek]

What is the meaning of '--' sign. I think it is to multiply the result with -1 but why???

http://www.mrexcel.com/forum/excel-questions/202204-find-last-cell-data-formular.html