Sumproduct + countif

[Stephanie Knutson]

Hello!

I have created the table below using the following formula for column AB: =SUMPRODUCT((G2:G170<>"")*(COUNTIF(G2:G170,G2:G170)=1)/(COUNTIF(G2:G170,G2:G170)+(G2:G170="")))

Capture.PNG

I want to be able to change the labels in column AA to say "between 2 and 6 sessions" (so a range) , rather than simply "more than 1" (>1) as it is now.

I tried converting the COUNTIF portion of the formula above to COUNTIFS but was not successful. Do I need to add (AND( somewhere?

Thank you!!!

[FlameRetired]

Hello Stephanie Knutson. Welcome to the forum.

You usually get more and faster response if you attach an Excel workbook representative of what you are working with and what you hope to achieve.

If you are not familiar with how to do this ...
Of note: The 'paperclip' icon has not worked for some time. So please save yourself some grief.
To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),

be sure to desensitize the data

• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.