+ Reply to Thread
Results 1 to 13 of 13

COUNTIFS formula convert to 2003

  1. #1
    Registered User
    Join Date
    09-16-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    COUNTIFS formula convert to 2003

    Please would someone be kind enough to help me convert this formula so that people using 2003 can see the data:

    =COUNTIFS('Sales'!$A$4:$A$400,"JAN",'Sales'!$V$4:$V$400,1)

    Would really appreciate any help at all.
    Thank you!!!

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: COUNTIFS formula convert to 2003

    Try:

    =SUMPRODUCT(('Sales'!$A$4:$A$400="JAN")*('Sales'!$V$4:$V$400=1))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    09-16-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: COUNTIFS formula convert to 2003

    Just tried it and it has come back with an error #####VALUE

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: COUNTIFS formula convert to 2003

    Is the column wide enough to display the result?

  5. #5
    Registered User
    Join Date
    09-16-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: COUNTIFS formula convert to 2003

    yes, I have just checked and I have also expanded it further just incase.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: COUNTIFS formula convert to 2003

    So are you getting ######### or are you getting #VALUE! error?

    If the Countifs worked, so should the Sumproduct.

  7. #7
    Registered User
    Join Date
    09-16-2010
    Location
    London
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: COUNTIFS formula convert to 2003

    Apologies it is a #VALUE!

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: COUNTIFS formula convert to 2003

    So then I think you must have that error in one of the 2 lookup columns.... it's needs to be fixed.

  9. #9
    Registered User
    Join Date
    09-18-2010
    Location
    Kalamazoo, MI
    MS-Off Ver
    Excel 2007
    Posts
    37

    Re: COUNTIFS formula convert to 2003

    Can you attach your workbook? My guess is that your numbers are probably stored as text, but I'm not really sure if that would affect SUMPRODUCT.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: COUNTIFS formula convert to 2003

    Text entries won't affect this sumproduct formula.

  11. #11
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: COUNTIFS formula convert to 2003

    Do you have a comparison statement in all parts of the sumproduct? If so, the formula should work, unless your ranges are different in size. The * operator in Sumproduct will throw an error if one of the component of the sumproducts returns text. A formula like

    =SUMPRODUCT(('Sales'!$A$4:$A$400="JAN")*'Sales'!$V$4:$V$400)

    will throw an error if column V contains text. In that case, use the -- version, like

    =SUMPRODUCT(--('Sales'!$A$4:$A$400="JAN"),'Sales'!$V$4:$V$400)

    For a discussion of Sumproduct and its error messages, see here http://spreadsheet-toolbox.com/libra...error-message/

    ... and no, I don't want your email address, and no, I'm not trying to sell you Excel information that you can get for free if you want.
    Last edited by teylyn; 09-22-2010 at 04:54 AM.

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: COUNTIFS formula convert to 2003

    properly laid out data would enable a Pivottable to do this
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  13. #13
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: COUNTIFS formula convert to 2003

    Did you resolve this?

    COUNTIFS will ignore errors in the ranges but the suggested SUMPRODUCT and other formulas won't ignore those errors. It would be best to eliminate any errors in the referenced ranges but if you don't this formula might work for you

    =SUMPRODUCT(ISNUMBER(MATCH(Sales!$A$4:$A$400,{"JAN"},0))*ISNUMBER(MATCH(Sales!$V$4:$V$400&"",{"1"},0)))

    That will ignore errors as COUNTIFS does and also it will count both numeric 1s and text "1"s as COUNTIFS does
    Audere est facere

+ 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