count without repeting with condition

**sarinky** · 03-17-2013, 10:13 PM

Hi Everyone,

Previously, I got my thread solved on "Count without repeting".
=sumproduct((AA<>"")/countif(AA,AA&"")

But now I cannot figure out when it comes to a condition. For example,
I want to count the name of item in col A (without repeting) when the QTY in Col B is 2.

Please see the attached example for clearer view.

Regards,
Sarin

**Tony Valko** · 03-17-2013, 10:23 PM

Try this array formula**:

=SUM(IF(FREQUENCY(IF(B2:B16=2,MATCH(A2:A16,A2:A16,0)),ROW(A2:A16)-ROW(A2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

**benishiryo** · 03-17-2013, 10:24 PM

hi Sarin, try this array formula:
=SUM(SIGN(FREQUENCY(IF(B2:B16=2,MATCH(A2:A16,A2:A16,0)),ROW(A2:A16)-ROW(A2)+1)))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.