Hi - I wasn't able to find anything online to help me with grouping data using multiple measures

My Klip and formula is below

I'm simply looking to sum more than just "DISPENSED" , there are other values like DISPENSED SUN and DISPENSED READER that I am trying to sum in this column

Thanks!

• Janice Janczyn

Hi Kevin,

I can't see all your data, but I'll assume column A is the list of Clinic Names. Use the CONTAINS function to check for any items in column K that contain the word "DISPENSED" and Applied Actions to group and aggregate your data:

1. Clinic Name column, set formula to @A:A and on the Properties tab, select Group repeating labels.
2. Glasses Dispensed column, set formula to  IF( CONTAINS( @K:K, "DISPENSED"), 1, 0 ) and on the Properties tab, set Aggregation to Sum.

I'm not sure how your Lives Served column is calculated so haven't included it here, but similar logic would apply.

Thank you,
Janice

• Kevin Burke

Hi Janice,

Thanks for the quick response.

Is there a way I can sum more than just "DISPENSED" in Column K?

I am looking to sum more than one value such as

DISPENSED

DISPENSED SUN

DISPENSED ROC

Do I input more CONTAINS formulas inside the IF function?

Thanks again,

Kevin

• Janice Janczyn

Hi Kevin,

CONTAINS( @K:K, "DISPENSED") will return true for each item in column K that contains the word "DISPENSED" (e.g., DISPENSED, DISPENSED SUN, DISPENSED ROC) otherwise will return FALSE, so you only need 1 CONTAINS in the IF function.

Thanks,
Janice

• Kevin Burke

What if I need to return DISPENSED and something like SENT TO LAB

How can I sum different words like that ?

• Janice Janczyn

Hi Kevin,

You can use any combination of our logic functions in your IF condition. For example, OR( @K:K = "DISPENSED", @K:K = "SENT TO LAB") will return true if at least one of the conditions returns true. If you have further questions, please email support@klipfolio.com.

Thank you,
Janice