# 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.

 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
Friday, October 16, 2015 11:59 AM

• 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 Friday, October 16, 2015 1:33 PM
• Marked as answer by Friday, October 16, 2015 2:03 PM
Friday, October 16, 2015 1:26 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 Friday, October 16, 2015 12:37 PM
Friday, October 16, 2015 12:31 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.

Friday, October 16, 2015 1:12 PM
• 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 Friday, October 16, 2015 1:33 PM
• Marked as answer by Friday, October 16, 2015 2:03 PM
Friday, October 16, 2015 1:26 PM