Formula to Organize Data

[cantab04]

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:

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:

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.

 

[surkdidat]

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:

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:

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.

 

[cantab04]

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?

 

[mikerickson]

It sounds like a pivot table might be useful.

 

[surkdidat]

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]

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!