Counting cells only if cell X equals "yes"

Discussion in 'Microsoft Excel Worksheet Functions' started by Frustrated, Nov 5, 2009.

  1. Frustrated

    Frustrated Guest

    I am tracking my sales reps sales. I currently have a formula to track that,
    but now i need to track their sales only if they add a certian feature to the
    sale. So now i need to find a formula that tracks their sales if they answer
    yes to cell x. If they answer no to cell X i do not want to count that
    sale. Can someone please help. The formula that tracks their individual
    sales is COUNTIF($A$19:$A$3703, A10)
     
    Frustrated, Nov 5, 2009
    #1
    1. Advertisements

  2. Frustrated

    Peo Sjoblom Guest

    Please refrain from multiposting


    =SUMPRODUCT(--($A$19:$A$3703=A10),--($B$19:$B$3703="Yes"))

    --


    Regards,


    Peo Sjoblom


    "Frustrated" <> wrote in message
    news:...
    >I am tracking my sales reps sales. I currently have a formula to track
    >that,
    > but now i need to track their sales only if they add a certian feature to
    > the
    > sale. So now i need to find a formula that tracks their sales if they
    > answer
    > yes to cell x. If they answer no to cell X i do not want to count that
    > sale. Can someone please help. The formula that tracks their individual
    > sales is COUNTIF($A$19:$A$3703, A10)
     
    Peo Sjoblom, Nov 5, 2009
    #2
    1. Advertisements

  3. Frustrated

    Sean Timmons Guest

    so, you're saying you want to count if A19:A3703 equals A10 AND X3:X3703 =
    "yes"?

    =SUMPRODUCT(($A$19:$A$3703=A10)*($X$19:$X$3703="yes"))

    "Frustrated" wrote:

    > I am tracking my sales reps sales. I currently have a formula to track that,
    > but now i need to track their sales only if they add a certian feature to the
    > sale. So now i need to find a formula that tracks their sales if they answer
    > yes to cell x. If they answer no to cell X i do not want to count that
    > sale. Can someone please help. The formula that tracks their individual
    > sales is COUNTIF($A$19:$A$3703, A10)
     
    Sean Timmons, Nov 5, 2009
    #3
  4. Frustrated

    Jacob Skaria Guest

    When you have multiple critiera to be applied to return the count use one of
    the below

    ---When you have multiple criteria use SUMPRODUCT()
    '2 criterias
    =SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2))
    =SUMPRODUCT((A1:A10=F1)*(B1:B10=F2))

    '3 criterias
    =SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2)*(C1:C10=criteria3))
    'Wtih cells F1,F2,F3 holding the criteria
    =SUMPRODUCT((A1:A10=F1)*(B1:B10=F2)*(C1:C10=F3))

    ---In case you are using XL 2007 check out help on COUNTIFS()
    =COUNTIFS( Criteriarange1,Criteria1,Criteriarange2,Criteria2,...)


    If this post helps click Yes
    ---------------
    Jacob Skaria


    "Frustrated" wrote:

    > I am tracking my sales reps sales. I currently have a formula to track that,
    > but now i need to track their sales only if they add a certian feature to the
    > sale. So now i need to find a formula that tracks their sales if they answer
    > yes to cell x. If they answer no to cell X i do not want to count that
    > sale. Can someone please help. The formula that tracks their individual
    > sales is COUNTIF($A$19:$A$3703, A10)
     
    Jacob Skaria, Nov 6, 2009
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    if a:a (range) equals january and c:c equals gas then add g:g ($)

    Guest, Dec 29, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    330
    Guest
    Dec 29, 2005
  2. Guest

    If a cell equals _, at the next row that equals _, return value fr

    Guest, May 1, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    10
    Views:
    845
    Guest
    May 2, 2007
  3. GaryS

    Populate Cell with another cell, but only if the date equals today

    GaryS, Apr 30, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    947
    GaryS
    May 1, 2008
  4. dicko1

    If a cell equals another cell, then use the value in the cell to theright.

    dicko1, Jun 22, 2010, in forum: Microsoft Excel Worksheet Functions
    Replies:
    2
    Views:
    2,008
    dicko1
    Jun 22, 2010
  5. Steve
    Replies:
    4
    Views:
    290
    Steve
    Jan 22, 2013
Loading...

Share This Page