+ Reply to Thread
Results 1 to 9 of 9

Equivalent of countifs function for Excel 2003?

  1. #1
    Registered User
    Join Date
    02-02-2007
    Posts
    20

    Equivalent of countifs function for Excel 2003?

    I found a function that works great for what I need to do, in Excell 2007, but most of the pcs at my work have Excel 2003.

    What I need to do is compare each cell in two columns and if more than one critera is met, I need it to be counted. For example if a tech is a "cct3" and has between 111 points and 125 points, count him.

    Below is the formula from 2007 that works perfectly

    =COUNTIFS(C115:C123,"cct3",M115:M123,">110",M115:M123,"<126")

    This will not work for 2003 (no countifs), any ideas for a formula that might be just as funcional?

  2. #2
    Forum Contributor
    Join Date
    03-14-2006
    Location
    Pakistan
    Posts
    1,791

    Lightbulb

    Quote Originally Posted by oneyejack
    I found a function that works great for what I need to do, in Excell 2007, but most of the pcs at my work have Excel 2003.

    What I need to do is compare each cell in two columns and if more than one critera is met, I need it to be counted. For example if a tech is a "cct3" and has between 111 points and 125 points, count him.

    Below is the formula from 2007 that works perfectly

    =COUNTIFS(C115:C123,"cct3",M115:M123,">110",M115:M123,"<126")

    This will not work for 2003 (no countifs), any ideas for a formula that might be just as funcional?
    Try this

    =SUM(IF(C115:C123="cct3",IF(M115:M123>110,IF(M115:M123<126,1,0)),0))

    Its array formula so you have to press Ctrl+Shift+Enter (just Enter will not work)

  3. #3
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe

    =SUMPRODUCT(--(C115:C123="cct3")*(M115:M123>110)*(M115:M123<126))

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Just for fun....

    =SUMPRODUCT(-(C115:C123="cct3"),-(ABS(M115:M123-118)<8))

  5. #5
    Registered User
    Join Date
    02-02-2007
    Posts
    20
    I used the second function and it works perfect except for one cell that will need special consideration. There is a senario where a "cct lead" would have no points and therefore in the point total column m, a "" (blank would be displayed). I account for this tech by putting a "nr", which stands for no route in a seperate column g. What can I add to the following function to count a "cct lead" (column c), with "nr" (column g), and "" (column m)?

    =SUMPRODUCT(--(C4:C123="cct lead")*(M4:M123<51))

    "nr" has to be the key, because it accounts for a cct lead who is working and not routed, not all cct leads work every day. Therefore a lead who is working with nr and a lead who is not working both have "" in column m.

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Try

    =SUMPRODUCT(--(C4:C123="cct lead")*(G4:G123="nr")*(M4:M123<51))

    VBA Noob

  7. #7
    Registered User
    Join Date
    02-02-2007
    Posts
    20
    I tried that already, it will change the tally to zero. Doesn't even count the cct leads that do have points. My guess is because they do not meat all the criteria. I need a function that will tally if a cct lead has "nr" in column g or a number between 1 and 50 in column m. Here is a list of all possible outcomes and weither or not they should tally.

    Coulumn C G M Tally?
    cct lead nr "" yes working with no points
    cct lead "" "" no not working
    cct lead 1 23 yes working with points 1-50

    Sorry the outcomes are not lined up similar to Excel format, I don't know how to post that properly.
    Last edited by oneyejack; 09-29-2007 at 12:34 PM.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Try

    =SUMPRODUCT(--(C4:C123="cct lead"),(G4:G123="nr")+ISNUMBER(M4:M123)*(M4:M123< 51))

  9. #9
    Registered User
    Join Date
    02-02-2007
    Posts
    20
    You folks are great, thanks for all your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1