+ Reply to Thread
Results 1 to 5 of 5

COUNTIFS issue

  1. 04-11-2014, 04:56 PM #1
    Glen-
    Glen- is offline
    Registered User
    Join Date
    04-09-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    7

    COUNTIFS issue

    I am trying use a countifs statement to calculate multiple pieces of data.

    =COUNTIFS(Projects!L:L,"*G*",Projects!X:X,">="&"1/1/2011",Projects!X:X,"<="&"3/31/2011",Projects!L:L,"*F*",Projects!X:X,">="&"1/1/2011",Projects!X:X,"<"&"4/1/2011",Projects!K:K,"=3")


    Project is the master tab where the data is stored.
    Column L is the project names
    Column X is the project dates
    Column is a status

    What I am trying to is get the total of only these projects (that start with G and F) for a given date range. Counting the raw data, I should have 12. If I create statements for each project separately, I can get them to work by themselves. I get 5, which is the total of the G projects only. I think I am fairly close on this one.

    Any help is appreciated.

    Glen
    Reply With QuoteRegister To Reply
  2. 04-11-2014, 05:03 PM #2
    TMS
    TMS is online now
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,426

    Re: COUNTIFS issue

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


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 04-11-2014, 05:09 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,929

    Re: COUNTIFS issue

    =COUNTIFS(
    Projects!L:L,"*G*",
    Projects!X:X,">="&"1/1/2011",
    Projects!X:X,"<="&"3/31/2011",
    Projects!L:L,"*F*",
    Projects!X:X,">="&"1/1/2011",
    Projects!X:X,"<"&"4/1/2011",
    Projects!K:K,"=3")

    You are asking for a cell to = "*G*" AND "*F*" - I doubt excel likes that very much

    This is probably not adding to the problem, but you have doubled up on the start date, and if not conflicting, then at least duplicated, the end dates?

    Try putting your start/end dates in their own cell and then reference them - see if that helps?
    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. 04-11-2014, 05:09 PM #4
    etaf
    etaf is offline
    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,739

    Re: COUNTIFS issue

    the countifs() is a AND not an OR
    so the columns have to contain G AND F
    so you may want to do two sumifs() and add together

    =COUNTIFS(Projects!X:X,">="&"1/1/2011",Projects!X:X,"<="&"3/31/2011",Projects!L:L,"*F*",Projects!X:X,">="&"1/1/2011",Projects!X:X,"<"&"4/1/2011",Projects!K:K,"=3") + COUNTIFS(Projects!L:L,"*G*",Projects!X:X,">="&"1/1/2011",Projects!X:X,"<="&"3/31/2011",Projects!X:X,">="&"1/1/2011",Projects!X:X,"<"&"4/1/2011",Projects!K:K,"=3")

    you should also be able to use
    ">=1/1/2011"
    you only need the

    & if you using a CELL
    so
    ">="&C12

    also you have
    Projects!X:X,">="&"1/1/2011"
    twice
    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.
    Reply With QuoteRegister To Reply
  5. 04-11-2014, 08:29 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 issue

    See if this does what you want.

    A2 = 1/1/2011
    B2 = 3/31/2011

    =SUM(COUNTIFS(Projects!K:K,3,Projects!L:L,{"G*","F*"},Projects!X:X,">="&A2,Projects!X:X,"<="&B2))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    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. Countifs over multiple worksheets issue
    By luciedefreitas in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2014, 09:10 AM
  2. Issue with COUNTIFS formula and dates
    By cstockus in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 7
    Last Post: 11-27-2013, 08:05 PM
  3. COUNTIFS - Time Issue
    By kjcdude in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2013, 06:48 PM
  4. [SOLVED] Sumproduct as Countifs issue
    By jake.masters in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-12-2012, 10:49 AM
  5. [SOLVED] COUNTIF or COUNTIFS syntax issue
    By K Howe in forum Excel General
    Replies: 4
    Last Post: 03-19-2012, 02:20 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1