sumproduct and subtotal in the same formula

Jerickson said:

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

Click to expand...

Aladin Akyurek said:

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.

Click to expand...

Jerickson said:

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

Click to expand...