+ Reply to Thread
Results 1 to 12 of 12

Excel 2003 Countifs formula help please

  1. #1
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    111

    Excel 2003 Countifs formula help please

    hi all

    i am currently using excell 2011 at home however 2003 verison at work, i have done a spreadsheet that checks a column on the other sheet for a letter and then in tern counts up another column. the formula is =COUNTIFS('3 RIFLES DVR DATABASE'!$E$10:$E$528,"A",'3 RIFLES DVR DATABASE'!$I$10:$I$528,"<>")

    however when i open the spreasheet up in 2003 version i get a #NAME? error. does anyone have an idea on how i change the formula to get it to work or have a different formula i can try

    thank you

    mick

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Excel 2003 Countifs formula help please

    you can use sumproduct instead

    =SUMPRODUCT(--('3 RIFLES DVR DATABASE'!$E$10:$E$528 = "A"),--('3 RIFLES DVR DATABASE'!$I$10:$I$528 ?????))

    you have "<>" does not equal - what

    change the formula in 2011 at home and see that it works , and then 2003 should be ok
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Excel 2003 Countifs formula help please

    Hello mick
    Can you attach a sample please, I have 2007 but I think a sumproduct may work
    Peter

  4. #4
    Forum Contributor wakeupcall's Avatar
    Join Date
    02-29-2012
    Location
    Sofia, Bulgaria
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: Excel 2003 Countifs formula help please

    If I remeber correctly countifs and sumif were only introduced in Excel 2007 and they were not made compatible with Excel 2003
    i.e. Excel 2003 does not support those - only their single condition counterparts
    Liked the answer given? click * to say so

  5. #5
    Forum Contributor
    Join Date
    05-29-2009
    Location
    Kalgoorlie Australia
    MS-Off Ver
    Excel 2007/10
    Posts
    251

    Re: Excel 2003 Countifs formula help please

    Too slow sorry
    Peter

  6. #6
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Excel 2003 Countifs formula help please

    hi mick, try:
    =SUMPRODUCT(--('3 RIFLES DVR DATABASE'!$E$10:$E$528="A"),--('3 RIFLES DVR DATABASE'!$I$10:$I$528<>""))

    Quote Originally Posted by etaf View Post
    you have "<>" does not equal - what
    that in COUNTIF means does not equals to blank.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  7. #7
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    111

    Re: Excel 2003 Countifs formula help please

    hi guys

    etaf

    the "<>" was used so it would count up anything in that range.

    guys please find attached a the worksheet

    in the workings sheet you will see countifs works but not in 2003 i get #Name? error

    i have had to delete alot of sheets to be able to upload it on here, but you still have main sheet,workings and A Coy tab
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    111

    Re: Excel 2003 Countifs formula help please

    Ben, Gents

    Sumproduct worked a treat, yet again thank you so much for everyone that took the time to take a look and help with this post.

    great site, great people

    mick

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Excel 2003 Countifs formula help please

    thanks for clearing the "<>" up

  10. #10
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    111

    Re: Excel 2003 Countifs formula help please

    can i ask guys using

    =SUMPRODUCT(--('3 RIFLES DVR DATABASE'!$E$10:$E$528="A"),--('3 RIFLES DVR DATABASE'!$I$10:$I$528<>""))

    is it possible in sum product to check three columns? so column B as well as E and I

    mick

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

    Re: Excel 2003 Countifs formula help please

    You can keep adding conditions in that same format, so if you only want to include rows where column B = "x" change to this version

    =SUMPRODUCT(--('3 RIFLES DVR DATABASE'!$E$10:$E$528="A"),--('3 RIFLES DVR DATABASE'!$I$10:$I$528<>""),--('3 RIFLES DVR DATABASE'!$B$10:$B$528="x"))
    Audere est facere

  12. #12
    Forum Contributor
    Join Date
    02-21-2013
    Location
    newcastle
    MS-Off Ver
    2011
    Posts
    111

    Re: Excel 2003 Countifs formula help please

    Daddylonglegs

    thanks mate, think this post is totally solved

+ 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. Converting Countifs to 2003 formula
    By rgold in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 07-21-2011, 09:15 PM
  3. COUNTIFS formula convert to 2003
    By tjlong in forum Excel - New Users/Basics
    Replies: 12
    Last Post: 09-23-2010, 05:41 PM
  4. COUNTIFS formula under excel 2003
    By mihait85 in forum Excel General
    Replies: 3
    Last Post: 04-05-2010, 04:23 AM
  5. excel's new countifs...on 2003?
    By AndyBrown in forum Excel General
    Replies: 1
    Last Post: 07-20-2006, 11:25 AM

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