Formula to Organize Data

cantab04

New Member
Joined
Jan 12, 2018
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

I work for a university and am trying to conduct an analysis of the efficacy of our email campaigns over the past few years. I have raw data in a spreadsheet that looks like this:

apar5sz.png
vefTg





I'm looking for a way to collapse all email campaigns with the same title into a single label and sum their counts for each of the three outcome scenarios (No/No, Yes/No, and Yes/Yes).


Ideally I would end up with something like the image below:

TsHrCvm.png





Anyone have a recommendation for a formula I could use to accomplish this? This would be immensely helpful in determining the relative performance of 1,000+ email campaigns we've run over the years.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361
Hi there - first time trying to answer a query, not sure if I am being a bit too simplistic, but here goes..

I have the data in cells A1:c18 for my example[

I have created a table as per yours below with 9 different formulae
Didn't Click , Didn't Open
----------------------------
=COUNTIFS(A2:A18,A23,$B$2:$B$18,"NO",$C$2:$C$18,"NO") (A2 states ALUM)
=COUNTIFS(A3:A19,A24,$B$2:$B$18,"NO",$C$2:$C$18,"NO") (A3 states Welcome)
=COUNTIFS(A4:A20,A25,$B$2:$B$18,"NO",$C$2:$C$18,"NO") (A4 states First Basketball)

Opened, Not Clicked
-----------------------
=COUNTIFS(A2:A18,A23,$B$2:$B$18,"YES",$C$2:$C$18,"NO")
=COUNTIFS(A3:A19,A24,$B$2:$B$18,"YES",$C$2:$C$18,"NO")
=COUNTIFS(A4:A20,A25,$B$2:$B$18,"YES",$C$2:$C$18,"NO")

Opened & Clicked
-------------------
=COUNTIFS(A2:A18,A23,$B$2:$B$18,"YES",$C$2:$C$18,"YES")
=COUNTIFS(A3:A19,A24,$B$2:$B$18,"YES",$C$2:$C$18,"YES")
=COUNTIFS(A4:A20,A25,$B$2:$B$18,"YES",$C$2:$C$18,"YES")


(A2 is Opened, A3 is Clicked)

Hello everyone,

I work for a university and am trying to conduct an analysis of the efficacy of our email campaigns over the past few years. I have raw data in a spreadsheet that looks like this:

apar5sz.png
vefTg





I'm looking for a way to collapse all email campaigns with the same title into a single label and sum their counts for each of the three outcome scenarios (No/No, Yes/No, and Yes/Yes).


Ideally I would end up with something like the image below:

TsHrCvm.png





Anyone have a recommendation for a formula I could use to accomplish this? This would be immensely helpful in determining the relative performance of 1,000+ email campaigns we've run over the years.
 
Last edited:

cantab04

New Member
Joined
Jan 12, 2018
Messages
8
Office Version
  1. 365
Platform
  1. Windows
This is quite helpful, thanks! It seems like your formula necessitates manually selecting rows that have the same campaign title. Any chance there's a way of simply telling Excel to consolidate campaign titles and sum their associated data?
 

surkdidat

Active Member
Joined
Oct 1, 2011
Messages
361
Where I have A2,A3, and A4, this is referring to the campaign names. If in those boxes you just type the full name of the campaign, and reference it against the campaign name in your data sheet, that should work (Sorry my example I just used part of the name as an example!"

This is quite helpful, thanks! It seems like your formula necessitates manually selecting rows that have the same campaign title. Any chance there's a way of simply telling Excel to consolidate campaign titles and sum their associated data?
 

cantab04

New Member
Joined
Jan 12, 2018
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Where I have A2,A3, and A4, this is referring to the campaign names. If in those boxes you just type the full name of the campaign, and reference it against the campaign name in your data sheet, that should work (Sorry my example I just used part of the name as an example!"


THANK YOU--this is so helpful and much appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,536
Messages
5,596,748
Members
414,095
Latest member
George53

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top