Is the SUBTOTAL Formula correct in this instance?

koppahollic

New Member
Joined
Oct 26, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Some videos you may 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
Joined
Oct 26, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
May 2, 2008
Messages
35,677
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It sounds like you want:

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

Watch MrExcel Video

Forum statistics

Threads
1,114,443
Messages
5,547,951
Members
410,820
Latest member
Prepost
Top