# How can I calculate the subtotal for filtered cells that meet certain criteria?

I'm trying to create a subtotal for a range that excludes rows that don't have a wholesale price. I have a range of products, prices and units that have subtotals by brand, although the brand subtotal is a `sum` and not a `subtotal` because the total needs to be displayed regardless of what the user chooses to filter. These subtotal rows do not have wholesale prices.

Here is the sumif formula I'm using to calculate totals in the summary area above the range:

``````=SUMIF(B5:B12, "", D5:D12)
``````

I need to have a subtotal formula that works the same way. Is there an equivalent to the sumif formula for subtotals? Or maybe a worksheet function I can use?

I need to be able to do this without using VBA.

-

You can do conditional subtotals without VBA. Try this formula:

``````=SUMPRODUCT(SUBTOTAL(9,OFFSET(units,ROW(units)-ROW(D4),0,1)),--(wholesale=""))
``````

Where
`units` refers to `D4:D12` in your screenshot, with `D4` as the header
`wholesale` refers to `B4:B12` in your screenshot

Example - The formula at work in cell D2:

-
Perfect! I hope you get lots of blueberry cheesecake today. Glad I could help with the Excel and problem solving part. – Jon Crowell Jun 19 '12 at 17:37
Np! And welcome to Superuser! :) – Kaze Jun 19 '12 at 21:31
+1 - you could also use SUBTOTAL with 9 instead of 3 then you don't have to include units at the end of the SUMPRODUCT...... – barry houdini Jun 19 '12 at 22:03
@barryhoudini Facepalmed myself there. Nice catch. :D – Kaze Jun 19 '12 at 22:15