I am struggling with a datasheet that has the results of every email campaign I have run. It Contains columns:[TABLE="class: cms_table, width: 1333"]
<tbody>[TR]
[TD="class: cms_table_xl64, width: 286"]EMAIL[/TD]
[TD="class: cms_table_xl65, width: 120"]NAME[/TD]
[TD="class: cms_table_xl65, width: 256"]COMPANY[/TD]
[TD="class: cms_table_xl66, width: 81"]OPENS[/TD]
[TD="class: cms_table_xl65, width: 137"]TITLE[/TD]
[TD="class: cms_table_xl65, width: 143"]LIST[/TD]
[TD="class: cms_table_xl65, width: 310"]Email List[/TD]
[/TR]
</tbody>[/TABLE]
Email addresses appear on the sheet based on their presence on the email results, so many email addresses appear multiple times. I dont want to eliminate the duplicates, or consolidate them, they serve a purpose for me to reference the email with which they opened and its corresponding stats. I am trying to rank email addresses (openers) By their AVG which I have taken from 2 Columns I added: # of Emails opened, and Times Opened (Sum of "Opens" Column above for each corresponding email address for each email they opened)
I would like to find a conditional formula/function to take the sum of each "OPEN" IF there is a duplicate email address without consolidating the duplicates.
So far I have been doing the math by hand and using formulas/functions for the "Times Opened" and "AVG"
[TABLE="class: cms_table, width: 1524"]
<tbody>[TR]
[TD]EMAIL[/TD]
[TD]NAME[/TD]
[TD]COMPANY[/TD]
[TD]OPENS[/TD]
[TD]TITLE[/TD]
[TD]LIST[/TD]
[TD]Email List[/TD]
[TD]Times Opened Emails[/TD]
[TD]# EMAILS OPENED[/TD]
[TD]AVG[/TD]
[/TR]
[TR]
[TD]13IEUPS@BELLSOUTH.NET[/TD]
[TD]PEGGY SEGURA[/TD]
[TD]PEGGY SEGURA INC[/TD]
[TD]1[/TD]
[TD]Owner[/TD]
[TD]infoUSA[/TD]
[TD]MSSP Combined Lists Linkedin and InfoUSA[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GLENRAY@COX.NET[/TD]
[TD]GLEN RO\AY[/TD]
[TD]GLEN WELL SVC INC[/TD]
[TD]1[/TD]
[TD]Manager[/TD]
[TD]infoUSA[/TD]
[TD]MSSP Combined Lists Linkedin and InfoUSA[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MRFENCE@COX.NET[/TD]
[TD]ALDEN TRENT[/TD]
[TD]MR FENCE[/TD]
[TD]1[/TD]
[TD]Manager[/TD]
[TD]infoUSA[/TD]
[TD]MSSP Combined Lists Linkedin and InfoUSA[/TD]
[TD]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]MRFENCE@COX.NET[/TD]
[TD]ALDEN TRENT[/TD]
[TD]MR FENCE[/TD]
[TD]1[/TD]
[TD]Manager[/TD]
[TD]USA[/TD]
[TD]MPS 30% Off -USA List[/TD]
[TD]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AALBERT@FNB.COM[/TD]
[TD]ARLENE ALBERT[/TD]
[TD]FIRST NATIONAL BANK[/TD]
[TD]1[/TD]
[TD]Executive Officer[/TD]
[TD]USA[/TD]
[TD]MPS 30% Off -USA List[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
<tbody>[TR]
[TD="class: cms_table_xl64, width: 286"]EMAIL[/TD]
[TD="class: cms_table_xl65, width: 120"]NAME[/TD]
[TD="class: cms_table_xl65, width: 256"]COMPANY[/TD]
[TD="class: cms_table_xl66, width: 81"]OPENS[/TD]
[TD="class: cms_table_xl65, width: 137"]TITLE[/TD]
[TD="class: cms_table_xl65, width: 143"]LIST[/TD]
[TD="class: cms_table_xl65, width: 310"]Email List[/TD]
[/TR]
</tbody>[/TABLE]
Email addresses appear on the sheet based on their presence on the email results, so many email addresses appear multiple times. I dont want to eliminate the duplicates, or consolidate them, they serve a purpose for me to reference the email with which they opened and its corresponding stats. I am trying to rank email addresses (openers) By their AVG which I have taken from 2 Columns I added: # of Emails opened, and Times Opened (Sum of "Opens" Column above for each corresponding email address for each email they opened)
I would like to find a conditional formula/function to take the sum of each "OPEN" IF there is a duplicate email address without consolidating the duplicates.
So far I have been doing the math by hand and using formulas/functions for the "Times Opened" and "AVG"
[TABLE="class: cms_table, width: 1524"]
<tbody>[TR]
[TD]EMAIL[/TD]
[TD]NAME[/TD]
[TD]COMPANY[/TD]
[TD]OPENS[/TD]
[TD]TITLE[/TD]
[TD]LIST[/TD]
[TD]Email List[/TD]
[TD]Times Opened Emails[/TD]
[TD]# EMAILS OPENED[/TD]
[TD]AVG[/TD]
[/TR]
[TR]
[TD]13IEUPS@BELLSOUTH.NET[/TD]
[TD]PEGGY SEGURA[/TD]
[TD]PEGGY SEGURA INC[/TD]
[TD]1[/TD]
[TD]Owner[/TD]
[TD]infoUSA[/TD]
[TD]MSSP Combined Lists Linkedin and InfoUSA[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GLENRAY@COX.NET[/TD]
[TD]GLEN RO\AY[/TD]
[TD]GLEN WELL SVC INC[/TD]
[TD]1[/TD]
[TD]Manager[/TD]
[TD]infoUSA[/TD]
[TD]MSSP Combined Lists Linkedin and InfoUSA[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MRFENCE@COX.NET[/TD]
[TD]ALDEN TRENT[/TD]
[TD]MR FENCE[/TD]
[TD]1[/TD]
[TD]Manager[/TD]
[TD]infoUSA[/TD]
[TD]MSSP Combined Lists Linkedin and InfoUSA[/TD]
[TD]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]MRFENCE@COX.NET[/TD]
[TD]ALDEN TRENT[/TD]
[TD]MR FENCE[/TD]
[TD]1[/TD]
[TD]Manager[/TD]
[TD]USA[/TD]
[TD]MPS 30% Off -USA List[/TD]
[TD]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AALBERT@FNB.COM[/TD]
[TD]ARLENE ALBERT[/TD]
[TD]FIRST NATIONAL BANK[/TD]
[TD]1[/TD]
[TD]Executive Officer[/TD]
[TD]USA[/TD]
[TD]MPS 30% Off -USA List[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]