Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 2 down vote accepted

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:

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. –  Head of Catering Jun 19 '12 at 17:37
    
Np! And welcome to Superuser! :) –  Ellesa Jun 19 '12 at 21:31
1  
+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 –  Ellesa Jun 19 '12 at 22:15
add comment

Your Answer

 
discard

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.