determine value based on range of sub values

**shayma** · 09-14-2014, 11:09 AM

Hello

I'm new to this forum.

I have a list of categories listed on excel table, and each category could have (any) number of sub categories. The sub categories could have values YES or NO.

Category	Sub Category	Value
Category A	sub 1	YES
Category A	sub 2	NO
Category A	sub 3	YES
Category B	sub 1	NO
Category B	sub 2	NO
Category C	sub 1	NO
Category C	sub 2	NO
Category C	sub 3	YES
Category C	sub 4	YES
Category D	sub 1	YES

I want to create a summary table that marks Category X as YES only if all sub categories contain the value YES, as so the results would be:

Category	Value
Category A	NO
Category B	NO
Category C	NO
Category D	YES

How do I calculate the formula under the Value column in the second table? I have a feeling I have to use HLOOKUP in combination with something else (Countif? Indirect?) but I can't seem to get a grasp around it. I don't mind using helper columns.

**Ron Coderre** · 09-14-2014, 11:27 AM

With your first table in Sheet1...beginning in cell A1
and
your second table in Sheet2...beginning in cell A1

First, I converted your first table to an Excel Table (Home.Format_as_table...follow the prompts) for 2 reasons:
1) Tables are a great Excel feature
2) Tables make this example easier to understand

Then, on Sheet2, this formula begins the list of Yes/No categorization...Assigning YES only if there are not any NO values.

Please Login or Register  to view this content.

Is that something you can work with?

**shayma** · 09-14-2014, 11:50 AM

Thank you so much Ron, it worked like a charm

**Ron Coderre** · 09-14-2014, 12:01 PM

Glad you got something you can use!

If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)

...BTW: If anybody in this forum is ever particularly helpful, you can thank them by clicking the "Add Reputation" star under one of their posts.