PDA

View Full Version : Return value based on Multiple Conditions

GingaNinga
2017-08-31, 09:37 PM
Hello, thank you for your help.

What I am trying to accomplish is this:

Where unique ID column is the same & if at least one of the TL Gum Displays equals 1, I would like to return a 1 under "Unique Gum"
If unique ID is the same, and TL Gum Displays are 0, then it can be blank or 0

Bottom line, is I am trying to determine the number of unique within each column.

I have attached a sample file, with a screenshot of the expected result for clarity.

Bob Phillips
2017-08-31, 10:22 PM
Try

=--(SUMPRODUCT(--(\$A\$2:\$A\$20=A2),--(\$B\$2:\$B\$20=1))>0)

GingaNinga
2017-08-31, 10:58 PM
Try

=--(SUMPRODUCT(--(\$A\$2:\$A\$20=A2),--(\$B\$2:\$B\$20=1))>0)

Thanks for the response. Unfortuantely it retruns a value of 1 for all rows.

Bob Phillips
2017-09-01, 08:43 AM
That is because all rows meet bothconditions in your test data. Explain why it shouldn't.

GingaNinga
2017-09-01, 01:23 PM
That is because all rows meet bothconditions in your test data. Explain why it shouldn't.

I am trying to isolate the unique ones.

Lets say I have 3 rows where unique ID is the same, and I have a sum of 2 TL Gum displays within those 3 unique IDs

The value I want to return in unique gum is a single So my results would look like this:

Row ID TL Gum Unique Gum
1 1 1 1
2 1 0 0
3 1 1 0

So basically, if the unique ID is the same, and the sum of the TL Gum column is greater than 1 - I only want to count the first 1 in TL Gum.

I don't care which row 1 is returned in unique gum, and I don't care if the 0 values appear as null or 0's

You can also view the Sample Result attachment from my original post which also clarifies the result I am hoping to achieve

Hopefully this helps.