+ Reply to Thread
Results 1 to 9 of 9

Countifs not working when data is on second worksheet.

  1. 09-23-2015, 05:13 PM #1
    lans4rd
    lans4rd is offline
    Registered User
    Join Date
    12-27-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    33

    Countifs not working when data is on second worksheet.

    Hello,

    I'm trying to use the Countifs function to display the results on a 'Master' sheet using data on a sheet named 'DFW_FM'. I started by using the function that I was able to get working on the 'DFW_FM sheet then tried to modify on 'Master' to display the results in a paticular cell. Unfortunately, it's not working. Below are the two functions I've been using. The second has been tweeked a little with 'not working' being the final result. Not understanding what's wrong with it. Any help you can provide would be appreciated. Thank you.

    =COUNTIFS(D:D,"=UPT",A:A,">="&E3,A:A,"<="&E4) This one works.

    =COUNTIFS(DFW_FM!$D:$D,"=UPT",DFW_FM!$A:$A,">='Master'!$B4",DFW_FM!$A:$A,"'Master'!$<=B5") This one doesn't.
    Reply With QuoteRegister To Reply
  2. 09-23-2015, 05:15 PM #2
    protonLeah
    protonLeah is offline
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,620

    Re: Countifs not working when data is on second worksheet.

    ...."'Master'!$<=B5" .... ???

    Use Named Ranges:
    Select the used rows in column A and give them a descriptive name, Same for column D. Then use the names in the formula:


    =COUNTIFS( colDname,"=UPT",colAname,">='Master'!$B4",ColAname, ...)

    If you decide to modify the formula with named ranges, we can show you how to make them dynamic, i.e., auto adjusting...
    Last edited by protonLeah; 09-23-2015 at 05:35 PM.
    Ben Van Johnson
    Reply With QuoteRegister To Reply
  3. 09-23-2015, 05:16 PM #3
    Jonmo1
    Jonmo1 is offline
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Countifs not working when data is on second worksheet.

    Try
    =COUNTIFS(DFW_FM!$D:$D,"UPT",DFW_FM!$A:$A,">="&'Master'!$B4,DFW_FM!$A:$A,"<="&'Master'!B5)
    Reply With QuoteRegister To Reply
  4. 09-23-2015, 05:18 PM #4
    ben_hensel
    ben_hensel is offline
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Countifs not working when data is on second worksheet.

    Looks like you've got a syntax error with the second one; the COUNTIF family of functions have kind of a screwy way of handling boolean comparisons.

    Please Login or Register  to view this content.
    I think that will do it.

    ...Do you really need to search a million rows of data?
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
    Reply With QuoteRegister To Reply
  5. 09-23-2015, 07:57 PM #5
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Countifs not working when data is on second worksheet.

    Quote Originally Posted by ben_hensel View Post
    =COUNTIFS(DFW_FM!$D:$D, "=UPT", DFW_FM!$A:$A, ">="&'Master'!$B4, DFW_FM!$A:$A, "<="&'Master'!$B5)

    ...Do you really need to search a million rows of data?
    COUNTIFS is an efficient function.

    It calculates the references based on the used range.

    For example, if the data extends to row 100 and there is nothing in the rows below that row then the formula will only evaluate the references down to row 100 even though you might use the entire columns as range references.

    If the used range extends down to row 100 then both of these formulas are equally efficient:

    =COUNTIFS(DFW_FM!$D:$D,"UPT",DFW_FM!$A:$A,">="&'Master'!$B4,DFW_FM!$A:$A,"<="&'Master'!$B5)

    =COUNTIFS(DFW_FM!$D1:$D100,"UPT",DFW_FM!$A1:$A100,">="&'Master'!$B4,DFW_FM!$A1:$A100,"<="&'Master'!$B5)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  6. 09-24-2015, 05:58 PM #6
    lans4rd
    lans4rd is offline
    Registered User
    Join Date
    12-27-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Countifs not working when data is on second worksheet.

    Thank you for the reply. It looks like Jonmo1's solution fits what I'm doing and is the easiest for me to understand. I appreciate your help. One other quick question. What would you recommend is the best way to learn VBA? I'm getting tired of feeling like a hack with this stuff all the time.
    Reply With QuoteRegister To Reply
  7. 09-24-2015, 06:02 PM #7
    lans4rd
    lans4rd is offline
    Registered User
    Join Date
    12-27-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Countifs not working when data is on second worksheet.

    Worked perfectly. Thank you so much for the help. Do you have a recommendation on how to go about learning VBA?
    Reply With QuoteRegister To Reply
  8. 09-24-2015, 06:08 PM #8
    lans4rd
    lans4rd is offline
    Registered User
    Join Date
    12-27-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Countifs not working when data is on second worksheet.

    Thanks for the help. It works! The problem with the data is that I never know how long the row will be. I just grabed the whole thing hoping I could get away with it. Thank you again.
    Reply With QuoteRegister To Reply
  9. 09-24-2015, 06:12 PM #9
    lans4rd
    lans4rd is offline
    Registered User
    Join Date
    12-27-2013
    Location
    Oklahoma
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: Countifs not working when data is on second worksheet.

    Yep, they work. Thank everyone so much for the support. It's for a work project and it's going to make my life a little easier.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. (VBA) COUNTIFS using MID not working
    By newbi004 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2015, 08:11 AM
  2. [SOLVED] Countifs formula not working when referenced to another worksheet
    By Burt_100 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 03-18-2014, 06:11 PM
  3. [SOLVED] { } not working in countifs
    By Chetansuri in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-03-2014, 04:46 AM
  4. Countifs FOrmula Not working
    By cartica in forum Excel General
    Replies: 1
    Last Post: 10-30-2013, 01:34 PM
  5. [SOLVED] countifs working on extended ranges and different sheets not working
    By etaf in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2013, 02:23 PM
  6. Fills down a column using countifs, extracting data from another worksheet
    By Lifeseeker in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-07-2012, 12:26 PM
  7. COUNTIFS not working
    By nosenga in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2011, 04:38 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1