+ Reply to Thread
Results 1 to 3 of 3

Will a COUNTSIFS work for me ?

  1. #1
    Registered User
    Join Date
    02-02-2012
    Location
    Ireland
    MS-Off Ver
    Excel 2003
    Posts
    4

    Will a COUNTSIFS work for me ?

    Hoping someone can help on this one,

    The below sample is Job Ref numbers which I need to count at different times from month to month as our year progresses

    For example, If I wanted to count all the shifts between a certain date range. I think the problem is that my dates are going across in a row and not a column. ??
    I need to have it going this way as this data eventually is for a full year of work.

    There is 2 tabs, what I currently have, and what I'm trying to get working

    Have a look and see what you think...

    Thanks,
    Paul
    Attached Files Attached Files
    Last edited by paul0872829332; 09-13-2019 at 05:09 PM.

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Will a COUNTSIFS work for me ?

    Quote Originally Posted by paul0872829332 View Post
    I think the problem is that my dates are going across in a row and not a column
    Yes. From the COUNTIFS support page: ``Each additional range must have the same number of rows and columns as the criteria_range1 argument``.

    You can use SUMPRODUCT as follows, entered into P27 and copied down the column. But you have other mistakes, which I've corrected and highlighted in red.

    =SUMPRODUCT(($A$2:$Q$2>=$O$25)*($A$2:$Q$2<=$P$25)*($A$6:$Q$19=O27))

    In addition to flipping your use of ``<=`` and ``>=``, also note the need to use absolute references to $O$25 and $P$25.

    PS.... Alternatively, use O$25 and P$25 if you might additional pairs of dates, e.g. in Q25:R25, S25:T25, etc.
    Last edited by joeu2004; 09-13-2019 at 06:23 PM.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Will a COUNTSIFS work for me ?

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Dave

+ 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. populate a list on one work sheet from multiple work sheets that meet a criteria
    By Kelly Linton-Selkirk in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-21-2015, 03:56 PM
  2. [SOLVED] Macro Doesn't Work Through Button, Does Work Through Developer ->Macros Option
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-27-2013, 11:55 AM
  3. Macros stop to work when work sheet is protected. Run time error 1004
    By sellim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-18-2012, 01:14 AM
  4. Replies: 4
    Last Post: 11-04-2011, 12:37 PM
  5. Excel 2007 : Excel 2007 Countsifs challenge
    By Paarlite2 in forum Excel General
    Replies: 14
    Last Post: 05-13-2008, 07:14 PM
  6. Copy rows from work sheets to combine to one work sheet based on cell value
    By joemcmillen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-11-2008, 05:04 PM
  7. Replies: 2
    Last Post: 07-11-2006, 11:15 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