+ Reply to Thread
Results 1 to 4 of 4

Adapting =SUM(COUNTIFS... for Excel 2003

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

    Question Adapting =SUM(COUNTIFS... for Excel 2003

    hello all,

    i am in the process of buidling an excel spreadsheet to be used by a few people in a shared drive. i have managed to get a forumla to work for the majority of users (who all use Excel 2007) however there are a couple who are still using Excel 2003. does anyone have any ideas on how i would adapt the formula to work exactly the same. please.

    The current formula is:
    =SUM(COUNTIFS($H$125:$H$611,"=31/03/2014",$G$125:$G$611,{"Dog","Cat","Rabbit","Penguin","Monkey","Meerkat"}))

    Basically there is a table over columns G and H which the user enters the details. they enter the date in column H and select an animal from a drop down box in column G, cells 125:600 on both. If a certain date is entered (in this case 31/03/204) and either of the animals are selected from the box then a '1' appears in the cell where the formula is entered.

    Does anyone have any ideas or a formula i would be able to copy across is order to get it to work how i want it too.

    Thanks guys!!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Adapting =SUM(COUNTIFS... for Excel 2003

    Hi,

    This is equivalent to your current formula:

    =SUMPRODUCT(0+($H$125:$H$611=DATEVALUE("31/03/2014")),0+(ISNUMBER(MATCH($G$125:$G$611,{"Dog","Cat","Rabbit","Penguin","Monkey","Meerkat"},0))))

    Regards
    Click * below if this answer helped

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

  3. #3
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Adapting =SUM(COUNTIFS... for Excel 2003

    maybe

    =SUM((A1:A8=DATEVALUE("31/03/2014"))*((B1:B8="Dog")+(B1:B8="Cat")+(B1:B8="Rabbit")+(B1:B8="Penguin")+(B1:B8="Monkey")+(B1:B8="Meerkat")))

    or

    =SUM((A1:A8="31/03/2014")*((B1:B8="Dog")+(B1:B8="Cat")+(B1:B8="Rabbit")+(B1:B8="Penguin")+(B1:B8="Monkey")+(B1:B8="Meerkat")))

    as array formulas (confirm with ctrl+shift+enter)

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Adapting =SUM(COUNTIFS... for Excel 2003

    try this

    =SUMPRODUCT(($H$125:$H$611=DATE(2014,3,31))*($G$125:$G$611={"Dog","Cat","Rabbit","Penguin","Monkey","Meerkat"}))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

+ 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. [SOLVED] replacing countifs for excel 2003
    By martm10 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-07-2014, 06:55 AM
  2. COUNTIFS in Excel 2003
    By HelenJH in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-13-2013, 06:00 PM
  3. [SOLVED] COUNTIFS to work in Excel 2003
    By mmcgrail in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-28-2013, 09:59 AM
  4. Excel 2003 - No COUNTIFS
    By westbay in forum Excel General
    Replies: 3
    Last Post: 01-18-2012, 09:29 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