+ Reply to Thread
Results 1 to 6 of 6

Combining COUNTIFS and ISNUMBER

  1. 03-04-2015, 06:58 PM #1
    OverKnight
    OverKnight is offline
    Forum Contributor
    Join Date
    01-09-2009
    Location
    NJ/NY
    MS-Off Ver
    Excel 2007 & 2016
    Posts
    124

    Combining COUNTIFS and ISNUMBER

    Maybe combining these isn't the correct method, so my title may not be accurate, but here's what I need to do.

    Column Z contains the days of the week (Z2:Z5000). The actual dates are in column D, and I have =TEXT(D2, "dddd") in each cell in column Z to provide the text of each day of the week..
    Column H contains locations (H2:H5000). For example, the locations are 12-10A, 12-10B, 24-20A, 24-20B, etc.
    What I need a count of is how many location 12 and location 24 were on each day of the week. I just need the count of the cells containing 12 or 24; what comes after the hyphen isn't important. I've attached a sample sheet; I'd like to have these formulae in cells U2:U6 and W2:W6.

    Thank you.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-04-2015, 07:12 PM #2
    tigertiger
    tigertiger is offline
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Combining COUNTIFS and ISNUMBER

    Try this formula

    at cell U2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    ---->copy to U2:U6 and W2:W6
    Best regard, -)iger-/iger
    If you are pleased with a solution mark your post SOLVED.
    Reply With QuoteRegister To Reply
  3. 03-04-2015, 08:51 PM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Combining COUNTIFS and ISNUMBER

    Not that it makes that much difference here (unless you meed to count/sum Mon-Fri), but instead of using TEXT(D2,"ddddd"), I would have used this...
    =WEEKDAY(D2,2)
    It will give you a number from 1-7 (1 being Monday). This way you can, for instance, sum/count all values for weekdays, using <6 as an argument

    OK, on to your question...
    You need to keep your ranges the same size...
    =COUNTIFS($Z$2:$Z$5000,"Monday",$H$2:$H$5000,"12*")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 03-04-2015, 08:51 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Combining COUNTIFS and ISNUMBER

    Not that it makes that much difference here (unless you meed to count/sum Mon-Fri), but instead of using TEXT(D2,"ddddd"), I would have used this...
    =WEEKDAY(D2,2)
    It will give you a number from 1-7 (1 being Monday). This way you can, for instance, sum/count all values for weekdays, using <6 as an argument

    OK, on to your question...
    You need to keep your ranges the same size...
    =COUNTIFS($Z$2:$Z$5000,"Monday",$H$2:$H$5000,"12*")
    Reply With QuoteRegister To Reply
  5. 03-05-2015, 12:20 AM #5
    OverKnight
    OverKnight is offline
    Forum Contributor
    Join Date
    01-09-2009
    Location
    NJ/NY
    MS-Off Ver
    Excel 2007 & 2016
    Posts
    124

    Re: Combining COUNTIFS and ISNUMBER

    Thank you, tigertiger and Ford. Both of your solutions worked perfectly. It's great when more than one solution is provided to a problem, because I feel that I learn even more.
    Thanks again - Jim
    Reply With QuoteRegister To Reply
  6. 03-05-2015, 12:55 AM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Combining COUNTIFS and ISNUMBER

    Glad it worked for you, thanks for the feedback
    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. Combining Indirect with Countifs
    By arthurphil in forum Excel General
    Replies: 7
    Last Post: 01-27-2015, 08:13 AM
  2. [SOLVED] Countifs & Search (ISNumber) Function together
    By MeritaH in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-11-2014, 11:01 PM
  3. help with combining if(isnumber) and concatenate
    By jomama2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2013, 01:34 AM
  4. [SOLVED] Combining two COUNTIFS
    By fletch8701 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-19-2012, 11:07 PM
  5. Combining if(isnumber(search with Frequency
    By Marshall101 in forum Excel General
    Replies: 5
    Last Post: 10-15-2008, 10:15 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1