+ Reply to Thread
Results 1 to 4 of 4

countifs working on extended ranges and different sheets not working

  1. #1
    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,684

    countifs working on extended ranges and different sheets not working

    i have a countifs function working on a different sheet

    so the formula is
    =COUNTIFS(Sheet2!$B:$B,$A3,Sheet2!$AK:$AK,B$2)
    which works

    where i have a grid with the criteria

    this works OK
    and
    =COUNTIFS(Sheet2!$B1:$B1000,$A3,Sheet2!$AK1:$AK1000,B$2)
    also works

    BUT
    if i change the range of the first criteria range to more then one column it fails

    =COUNTIFS(Sheet2!$B1:$G1000,$A3,Sheet2!$AK1:$AK1000,B$2)

    it retunes value
    i have text in A3
    Face to face
    and i have tried
    =COUNTIFS(Sheet2!$B:$B,"=Face to face",Sheet2!$AK:$AK,B$2)

    so no idea why it fails if i extend the columns

    any ideas ?

    thanks
    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.

  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: countifs working on extended ranges and different sheets not working

    Hi,

    From office.microsoft.com: "Each additional range must have the same number of rows and columns as the criteria_range1 argument."

    So

    =COUNTIFS(Sheet2!$B1:$G1000,$A3,Sheet2!$AK1:$AP1000,B$2)

    would fix this issue, though obviously may have undesirable consequences, depending what's in columns AL:AP.

    You can do it with SUMPRODUCT:

    =SUMPRODUCT((Sheet2!$B1:$G1000=$A3)*(Sheet2!$AK1:$AK1000=B$2))

    Regards
    Click * below if this answer helped

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

  3. #3
    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,684

    Re: countifs working on extended ranges and different sheets not working

    brilliant - thanks for that - both work perfectly

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

    Re: countifs working on extended ranges and different sheets not working

    You're welcome.

+ 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] Countifs code not working
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-08-2013, 06:09 AM
  2. [SOLVED] VBA coding only working in the first sheet. It is not working in all the sheets
    By srinivasan1965 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 09-16-2012, 02:04 PM
  3. COUNTIFS not working
    By nosenga in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2011, 04:38 AM
  4. Working named ranges in multiple sheets from vba
    By brucemc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-20-2007, 09:07 PM
  5. [SOLVED] Auto Extended Formula not working
    By Connie Martin in forum Excel General
    Replies: 2
    Last Post: 03-23-2006, 12:30 PM

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