# Subtotal IF

#### DinaMae

##### New Member
Hi, this might sound weird..but I'm doing the reverse engineering way.

I got this formula :
= SUMPRODUCT(SUBTOTAL(9,OFFSET(B2:B6,ROW(B2:B6)-MIN(ROW(B2:B6)),0,1)),(C2:C6="Y")+0)

and applied to my requirement:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(M4:M773,ROW(M4:M773)-MIN(ROW(M4:M773)),0,1)),(H4:H773="P")+0)

which perfectly worked on what I am looking for.... but I want to understand how the formula really works, what is the logic behind. So it would be easy for me to apply it when a similar scenario comes out.

Thanks.

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
Hi
Welcome to the board

=SUMPRODUCT(M4:M773,(H4:H773="P")+0)

that adds the values in column M for the values in column H equal to "P".

The difference is that you want to apply a filter to the range and ignore the rows that that are hidden by the filter.

OFFSET(M4:M773,ROW(M4:M773)-MIN(ROW(M4:M773)),0,1) generates an array with the individual references for each cell in the range: (M4,M5, ... ,M773)

SUBTOTAL(9,OFFSET(M4:M773,ROW(M4:M773)-MIN(ROW(M4:M773)),0,1))

generates an array with a value for each of the cells:
- If the cell is visible, SubTotal() function returns the value of the cell.
- If the cell is not visible, because a filter was applied, the SubTotal() function returns 0.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(M4:M773,ROW(M4:M773)-MIN(ROW(M4:M773)),0,1)),(H4:H773="P")+0)

adds the values in column M, ignoring the ones that hidden due to a filter, when the cells in column H in the same row have the value "P".

Does this help?

Ok I got it PGC! thanks alot.. =)

You're welcome. Thanks for the feedback.

Replies
5
Views
685
Replies
7
Views
1K
Replies
2
Views
146
Replies
7
Views
321
Replies
3
Views
610

### Forum statistics

1,196,457
Messages
6,015,368
Members
441,889
Latest member
balolaptopgiaolong

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back