+ Reply to Thread
Results 1 to 11 of 11

replacing countifs for excel 2003

  1. #1
    Registered User
    Join Date
    05-07-2014
    Location
    cardiff
    MS-Off Ver
    Excel 2003
    Posts
    10

    replacing countifs for excel 2003

    hi, i currently have a working forumla for excel 2007 which is =COUNTIFS($H$2:$H$4000, ">="&$P$2,$H$2:$H$4000,"<="&$P$3,$E$2:$E$4000,"=Dog") however i am trying to adapt it for someone who only has access to excel 2003. please could someone help me with this??

    The cells in column E is a drop down list containing options such as 'dog' 'cat' etc.
    Cell P2 contains the start date of the spreadsheet
    Column H contains dates which the user enters.
    The overall result only counts entries which have both Dog written in column E and a valid date entered in column H.
    Please can someone help me with this??

    Many thanks,

  2. #2
    Forum Contributor
    Join Date
    10-07-2011
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    212

    Re: replacing countifs for excel 2003

    use sumproduct
    =sumproduct(($H$2:$H$4000">="&$P$2)*($H$2:$H$4000"<="&$P$3)*($E$2:$E$4000"=Dog")*(1))

    Regards,
    Suhas

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: replacing countifs for excel 2003

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Registered User
    Join Date
    05-07-2014
    Location
    cardiff
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: replacing countifs for excel 2003

    Thanks for the reply,
    i have pasted the forumla into the spreadsheet, however it is still not working. the first $H$4000'' seems to be highlighted as the problem?
    What is the (1) for at the end btw?

    Thanks,

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: replacing countifs for excel 2003

    Did you checked post#3??

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,269

    Re: replacing countifs for excel 2003

    It's the " that is the problem Look again at the suggestions offered.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    05-07-2014
    Location
    cardiff
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: replacing countifs for excel 2003

    yep, just saw post #3 - worked perfectly thank you!!
    i also have another slight problem, for one of the cells i need it to count if 1) a valid date is entered and 2) either 'dog' or 'cat' are entered. At the moment i have =COUNTIFS($H$2:$H$4000, ">="&$P$2,$H$2:$H$4000,"<="&$P$3,$E$2:$E$4000,"=Cat",$E$2:$E$4000,"=Dog") as my forumla however it doesnt seem to pick up when Dog is entered (only when cat is entered), please can someone help with both 2007 and 2003 versions of excel?

    Many thanks,

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: replacing countifs for excel 2003

    For 2007+:

    =SUM(COUNTIFS($H$2:$H$4000, ">="&$P$2,$H$2:$H$4000,"<="&$P$3,$E$2:$E$4000,{"Cat","Dog"}))

    for 2003 or earlier:
    =SUMPRODUCT(($H$2:$H$4000>=$P$2)*($H$2:$H$4000<=$P$3)*($E$2:$E$4000={"Cat","Dog"}))
    Remember what the dormouse said
    Feed your head

  9. #9
    Registered User
    Join Date
    05-07-2014
    Location
    cardiff
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: replacing countifs for excel 2003

    thank you romperstomper, still cant get it to work though :/ do you have any other suggestions please?

  10. #10
    Registered User
    Join Date
    05-07-2014
    Location
    cardiff
    MS-Off Ver
    Excel 2003
    Posts
    10

    Re: replacing countifs for excel 2003

    scrap the above post - works perfectly, was an error on my behalf! many thanks all for your help, i know where to come if i have any furhter problems!

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,269

    Re: replacing countifs for excel 2003

    If you wish to thank someone here, you can do so by adding to their reputation ( the * icon below their user name).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. COUNTIFS in Excel 2003
    By HelenJH in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2013, 06:00 PM
  2. [SOLVED] Countifs alternative for Excel 2003
    By Jigz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-02-2012, 01:12 PM
  3. Excel 2003 - No COUNTIFS
    By westbay in forum Excel General
    Replies: 3
    Last Post: 01-18-2012, 09:29 AM
  4. Converting COUNTIFS to Excel 2003
    By John Bates in forum Excel General
    Replies: 2
    Last Post: 07-06-2009, 11:24 AM
  5. excel's new countifs...on 2003?
    By AndyBrown in forum Excel General
    Replies: 1
    Last Post: 07-20-2006, 11:25 AM

Tags for this Thread

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