Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

Incorrect Subtotals

share|improve this question
up vote 2 down vote accepted

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


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:

enter image description here

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.