Answered by:
Fun Counting Formula Question
Question

Good morning all,
I have a question to start the day.
In the example below, I need a formula that I can put in a cell that is going to count the number of unique 'Order No's', but only when Stock =1. So in the example below, it would return the value of 6 because there are 6 individual orders that have at least a stock value of 1.
So far all of attempts at countif and sumif formulas have failed.
Can someone please help?
Order No Stock C48498 1 C48498 1 D48438 1 D48452 1 D48452 1 D48467 1 E48633 1 E48633 1 J48466 J48466 J48466 J48466 K48754 1
Answers

You have to specify ranges of the same size, so you should use D2:D27 instead of D:D. Also, you can't use AND here  AND returns only a single TRUE/FALSE value.
This should work:
=SUM(IF((Misses!D2:D27=A1)*(Misses!I2:I27=1),1/COUNTIFS(Misses!E2:E27,Misses!E2:E27,Misses!D2:D27,A1,Misses!I2:I27,1)))
again confirmed with Ctrl+Shift+Enter.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by André Santo Friday, October 16, 2015 1:33 PM
 Marked as answer by pete6256 Friday, October 16, 2015 2:03 PM
All replies

For example as an array formula, confirmed with Ctrl+Shift+Enter (this is essential):
=SUM(IF(B2:B14=1,1/COUNTIFS(A2:A14,A2:A14,B2:B14,1)))
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by André Santo Friday, October 16, 2015 12:37 PM

Thank you Hans, it worked perfectly.
One last question though sir, I tried to modify the formula to include and extra count criteria but it didn't seem to work? Is there anything you can suggest:
=SUM(IF(AND(Misses!D:D=A1,Misses!I2:I27=1),1/COUNTIFS(Misses!E2:E27,Misses!E2:E27,Misses!I2:I27,1)))
A1 actually contains todays date.

You have to specify ranges of the same size, so you should use D2:D27 instead of D:D. Also, you can't use AND here  AND returns only a single TRUE/FALSE value.
This should work:
=SUM(IF((Misses!D2:D27=A1)*(Misses!I2:I27=1),1/COUNTIFS(Misses!E2:E27,Misses!E2:E27,Misses!D2:D27,A1,Misses!I2:I27,1)))
again confirmed with Ctrl+Shift+Enter.
Regards, Hans Vogelaar (http://www.eileenslounge.com)
 Proposed as answer by André Santo Friday, October 16, 2015 1:33 PM
 Marked as answer by pete6256 Friday, October 16, 2015 2:03 PM