I have a spreadsheet of data (Excel 2016) analyzing newsletter results for our firm. Column A has client email addresses and each column to the right has the following – Column B contains “X” if the client was sent the newsletter for that month, column C contains either “O” or “DNO” depending on if the client opened it or not. It then repeats for the next newsletter (Column D has “X” if they were sent & columen E has the result of that send “O” or “DNO”).
Here is my problem.
I would like to create a summary sheet that displays the client’s email, how many times they were sent a newsletter, how many they opened, and how many they didnt. Does anyone know of a formula that can do this?
I have tried to think of ways of incorporating the VLOOKUP formula with the a variation of the COUNT formula, but the problem is the inital sheet is sortable/filterable, so the position of a contact record might change (thus changing the row the COUNT function would be searching).
Any help is appreciated. I can provide more info if needed too.