Excel Formula for the below

Question

Please help me know the formula for below,

if the input is,

Header1 Header2 Header3
ABC DEF Yes
ABC DEF Yes
ABC GHI No
ABC GHI No
ABC GHI No
ABC GHI No
ABC GHI No
ABC GHI No
ABC GHI No
ABC JKL Yes

Then the output should be

Yes 2
No  1

We have to calculate Yes and No basis the uniqueness of Header 2, for eg: DEF is written twice so Yes counter is 1, then JKL has Yes, which makes Yes - 2. Similarly GHI is written once in header 2, so the counter of No is 1

Possible duplicate of [Count Unique values with a condition](https://stackoverflow.com/questions/15944249/count-unique-values-with-a-condition) — Scott Craner, Aug 28 '17 at 14:59
We have to calculate Yes and No basis the uniqueness of Header 2, for eg: DEF is written twice so Yes counter is 1, then JKL has Yes, which makes Yes - 2. Similarly GHI is written once in header 2, so the counter of No is 1. — Neha Kohli, Aug 28 '17 at 15:05

score 0 · Answer 1 · edited Sep 02 '17 at 12:15

Try this array formula in the cell where you want the answer presuming your data headers are in row 1 and the data starts in Cell A2 and ends in Cell C11:

=SUM(IF(FREQUENCY(IF(C$2:C$11="Yes",IF(B$2:B$11<>"",MATCH(B$2:B$11,B$2:B$11,0))),ROW(B$2:B$11)-ROW(B$2)+1),1))

When you have it typed in, press Ctrl+Shift+Enter keys at the same time. That will put the { and } brackets into the formula. Typing the brackets will not work since it is an array formula.

Simply change out the "Yes" with "No" for the no responses.

Excel Formula for the below

1 Answers1