Count IF Question

Hello all, just me again with another Count IF question!!! I was curious if anyone could assist with a question of mine. I am using 2 Sheets. On Sheet A I have Audience Names that I am trying to match back to another sheet and see how many different campaigns are tied to the Audiences. I currently am using a count if formula to see how many times the Audience Name is referenced within sheet B. In this example: "Launch_DGU_BHT_C9X_FY158_FI_US_Conquest - P1_M/F_18+_PREMIU_Premium" from Sheet A appears in Sheet B 37 times but has 5 different campaigns tied to that one Audience. In the screenshots below you will the "Platform Audience Name" is what is being looked to match from Sheet A to Sheet B. However, I would like to create another column that shows the number of different Campaigns tied to 1 Audience - Or 5 for the screenshot example.

Can anyone help me identify the correct formula to use to get that 5 amount? Also is there a Index Collect/join Collect/lookup that will pull the 5 different Campaign Names to that one Audience back to Sheet A? Currently using an idex collect but it is only matching back the top most value from sheet A.

Apologies know there is a lot of information and 2 questions so please let me know if any additional context or information is necessary. Thank you for the time and assistance.



Sheet A - Launch_DGU_BHT_C9X_FY158_FI_US_Conquest - P1_M/F_18+_PREMIU_Premium

Sheet B - Launch_DGU_BHT_C9X_FY158_FI_US_Conquest - P1_M/F_18+_PREMIU_Premium referenced 37 times but has 5 different Campaign Names tied to it


Best Answer

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    You will have to adjust this to match what you are using for cross sheet reference names.

    I tend to stick with COUNTIFS even for a single condition over COUNTIF just because I prefer the consistency and syntax. To simply get the number of matches (sounds like you have this working already):

    =COUNTIFS({Ad Set Name}, [Platform Audience Name]@row)


    To get the number of DISTINCT campaigns for your specific audience:

    =COUNT(DISTINCT(COLLECT({Campaign Name}, {Ad Set Name}, [Platform Audience Name]@row)))


    To populate a cell with a list of the unique campaigns to match your audience name. I used a comma as a delimiter, but you can adjust that for another character if needed:

    =JOIN(DISTINCT(COLLECT({Campaign Name}, {Ad Set Name}, [Platform Audience Name]@row)), ",")

Answers

  • Carson Penticuff
    Carson Penticuff ✭✭✭✭✭✭
    Answer ✓

    You will have to adjust this to match what you are using for cross sheet reference names.

    I tend to stick with COUNTIFS even for a single condition over COUNTIF just because I prefer the consistency and syntax. To simply get the number of matches (sounds like you have this working already):

    =COUNTIFS({Ad Set Name}, [Platform Audience Name]@row)


    To get the number of DISTINCT campaigns for your specific audience:

    =COUNT(DISTINCT(COLLECT({Campaign Name}, {Ad Set Name}, [Platform Audience Name]@row)))


    To populate a cell with a list of the unique campaigns to match your audience name. I used a comma as a delimiter, but you can adjust that for another character if needed:

    =JOIN(DISTINCT(COLLECT({Campaign Name}, {Ad Set Name}, [Platform Audience Name]@row)), ",")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!