# Is the SUBTOTAL Formula correct in this instance?

#### koppahollic

##### New Member
I am simply trying to calculate a percent clicked figure (in regard to email campaign metrics) that will update accordingly when applying filters to the data.

In my spreadsheet for example.... In Column M (M5-204), I have the number of clicks and in Column G (G5-204) is the number of emails sent. I have a results section in rows 1-4 that re-calculates metrics based on when filters are applied to the data (sorting by email campaign for example). The calculation itself is simple... Column M divided by Column G will give you the percent clicked figure I'm after. But I can't figure out how to make that particular results cell automatically update when I filter the data.

I have been able to use the Subtotal Formula fine for calculating one column of data. But can this be used for calculating a formula with two columns of data like I'm trying to do above, if so do you mind sharing what that formula looks like?

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### koppahollic

##### New Member
The following formula won't calculate for me (just trying to get an email open % to be specific)...

=SUBTOTAL(2,SUM(M5:M204)/SUM(G5:G204))

Just the sum of one column divided by the sum of another column as a %. I am trying to get this to work so when I filter my data the cell will automatically update to reflect.

Is this possible?

#### RoryA

##### MrExcel MVP, Moderator
It sounds like you want:

=SUBTOTAL(9,M5:M204)/SUBTOTAL(9,G5:G204))

• koppahollic

#### koppahollic

##### New Member
It sounds like you want:

=SUBTOTAL(9,M5:M204)/SUBTOTAL(9,G5:G204))
Thank you, this formula worked. seeing it now makes sense

Replies
1
Views
27
Replies
3
Views
42
Replies
1
Views
32
Replies
3
Views
147
Replies
2
Views
72