sumproduct and subtotal in the same formula

[Jerickson]

Hey everyone, Im trying to figure out a formula. I really like the function sumproduct((a1:a24=a27)*(b1:b24)) reason I like it is because it only sums the information that I want. I want to go a step further and add subtotal to the mix.

I want a functioning formula like sumproduct((a1:a24=a27)*(subtotal(9,b1:24))

I have tried alot of variations to this and it still will not function. Reason I want this to work is so I can filter down my table even further to say the date. Im working on a very large tracking worksheet that I have pasted below.

Any advice would be great.

Thanks

http://img530.imageshack.us/my.php?image=sumscreenshot.jpg

 

[Aladin Akyurek]

Hey everyone, Im trying to figure out a formula. I really like the function sumproduct((a1:a24=a27)*(b1:b24)) reason I like it is because it only sums the information that I want. I want to go a step further and add subtotal to the mix.

I want a functioning formula like sumproduct((a1:a24=a27)*(subtotal(9,b1:24))

I have tried alot of variations to this and it still will not function. Reason I want this to work is so I can filter down my table even further to say the date. Im working on a very large tracking worksheet that I have pasted below.

Any advice would be great.

Thanks

http://img530.imageshack.us/my.php?image=sumscreenshot.jpg

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B24,ROW(B2:B24)-ROW(B2),,1)),--(A2:A24=A27))

The formula assumes that A1:B1 houses the relevant headers. Adjust the ranges to suit.

 

[Jerickson]

wow was that fast. Thank you so much

 

[Jerickson]

Try...

=SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B24,ROW(B2:B24)-ROW(B2),,1)),--(A2:A24=A27))

The formula assumes that A1:B1 houses the relevant headers. Adjust the ranges to suit.

I dont mean to be a pest. But I really love learning. I understand most of the formula above except for the -- part is that minus the negative if so could you just use + or is it something in excel that is a secret trick.

Thanks

 

[Aladin Akyurek]

I dont mean to be a pest. But I really love learning. I understand most of the formula above except for the -- part is that minus the negative if so could you just use + or is it something in excel that is a secret trick.

Thanks

It effects the so-called coercion, which we need in a SumProduct formula with the conditional terms like the last part in the formula i question. See also:

http://www.mrexcel.com/forum/showthread.php?t=70547

http://www.mrexcel.com/forum/showthread.php?t=128907

 

[Jerickson]

wow there is so much more I have to learn.

Thanks again.