# SUMIF Function for cells that will be filtered (SUBTOTAL)

1. ## SUMIF Function for cells that will be filtered (SUBTOTAL)

I have attached an excerpt from my excel sheet.

I need to sum by category (the different markups). I have worked this out previously using a SUMIF functions which you will see used in the table on right.

I would like to use embed the SUBTOTAL, as I will be filtering some cells in my main sheet. How can I incorporate into my calculation the subtotal.  Register To Reply

2. ## Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

M3 cell , formula , drag down

HTML Code:
``=SUMPRODUCT(SUBTOTAL(9,OFFSET(\$G\$1,ROW(\$1:\$12),,,))*(\$B\$2:\$B\$13=\$L3))``  Register To Reply

3. ## Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

Another option with a helper column to avoid volatile functions.
In I2 copied down
Formula:  `Please Login or Register  to view this content.`
and then in M3 copied down
Formula:  `Please Login or Register  to view this content.`  Register To Reply

4. ## Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

Could you explain you mean by volatile?  Register To Reply

5. ## Re: SUMIF Function for cells that will be filtered (SUBTOTAL) Originally Posted by wk9128 M3 cell , formula , drag down

HTML Code:
``=SUMPRODUCT(SUBTOTAL(9,OFFSET(\$G\$1,ROW(\$1:\$12),,,))*(\$B\$2:\$B\$13=\$L3))``
Hi this solves my problem, thanks. Would it be possible for you to explain what the offset part of the function does?  Register To Reply

6. ## Re: SUMIF Function for cells that will be filtered (SUBTOTAL) Originally Posted by Fluff13 Another option with a helper column to avoid volatile functions.
In I2 copied down
Formula:  `Please Login or Register  to view this content.`
and then in M3 copied down
Formula:  `Please Login or Register  to view this content.`
Could you explain you mean by volatile?  Register To Reply

7. ## Re: SUMIF Function for cells that will be filtered (SUBTOTAL)

A volatile function (such as offset) will recalculate whenever any cell in the workbook is changed, or any formula recalculates.
Depending on what you workbook is like, volatile functions could make the workbook very slow.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 