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

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