none
Fun Counting Formula Question RRS feed

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

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