+ Reply to Thread
Results 1 to 6 of 6

Multiple sheets - Count all Occurances of the word "Early" if between two dates

Hybrid View

lookingforhelp1 Multiple sheets - Count all... 07-13-2015, 06:13 AM
Pauleyb Re: Multiple sheets - Count... 07-13-2015, 01:17 PM
JohnTopley Re: Multiple sheets - Count... 07-13-2015, 01:26 PM
Tony Valko Re: Multiple sheets - Count... 07-13-2015, 02:18 PM
JeteMc Re: Multiple sheets - Count... 07-13-2015, 01:49 PM
lookingforhelp1 Re: Multiple sheets - Count... 07-13-2015, 03:00 PM
  1. #1
    Registered User
    Join Date
    02-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    39

    Multiple sheets - Count all Occurances of the word "Early" if between two dates

    Hi

    I'm looking for some help on my spreadsheet.

    I have multiple Sheets (named January through to December).

    Each contains a list of possible dates within the month in column A3 down.

    Then, accross several columns on each of the sheets, it could say "Early", "Late" and "On time".

    What I am trying to achieve (yet really struggling) is to look on each sheet and if the date in column A falls between a specified date range (which is typed into 2 cells on my summary sheet - start and end date), I need a formula to count all occurances of the word "Early".

    Is there anyone who can give me any pointers on the best formula to achieve this, please?

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Multiple sheets - Count all Occurances of the word "Early" if between two dates

    I tentatively tried the COUNTIFS function spanning across sheets. It did not work. To double check, I did a search and came across this thread which confirmed my suspicions:
    http://www.excelforum.com/excel-form...le-sheets.html

    So, the pointers are:
    You cannot use the sheet spanning feature on COUNTIFS
    You want to use COUNTIFS and not COUNTIF
    You could have static cells in each sheet which perform the COUNTIFS for each sheet and then on your 'main' sheet just sum up those cells (using SUM with sheet spanning if you wish)
    OR you could write a formula in your main sheet which performs a COUNTIFS for each of your monthly sheets (i.e. COUNTIFS(jan...)+COUNTIFS(feb...)+...+COUNTIFS(dec...)
    OR you could try that suggested named range trick suggested by vlady in the link, but I did not confirm that would work
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,767

    Re: Multiple sheets - Count all Occurances of the word "Early" if between two dates

    Try this as a model ....

    =SUMPRODUCT(SUMIF(INDIRECT("'"&R3:R10&"'!f6:f500"),B3,INDIRECT("'"&R3:R4&"'!H6:H500")))

    R4:R10 is a list of your sheets which equally could be replaced by a named range

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Multiple sheets - Count all Occurances of the word "Early" if between two dates

    Quote Originally Posted by JohnTopley View Post

    =SUMPRODUCT(SUMIF(INDIRECT("'"&R3:R10&"'!f6:f500"),B3,INDIRECT("'"&R3:R4&"'!H6:H500")))

    R4:R10 is a list of your sheets which equally could be replaced by a named range
    In the formula you have 2 different ranges and in you description you have another!

    R3:R10
    R3:R4
    R4:R10
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,885

    Re: Multiple sheets - Count all Occurances of the word "Early" if between two dates

    I see that you already have several excellent answers, however since I started working on this before the others were offered, I thought I would add it anyway.

    Summary by Months.xlsx

    Hope this helps.

  6. #6
    Registered User
    Join Date
    02-23-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    39

    Re: Multiple sheets - Count all Occurances of the word "Early" if between two dates

    Thank you every one for your help so far, I will give all these a try tomorrow morning and report back.

    Unfortunately, I didn't set up the spreadsheet I'm building the summary page for which has made things a little bit more complicated all round.

+ 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] (Challenge) Sum Order From Count "Center" Word To Start Word & "Center" Word To End Word
    By sanju2323 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-31-2015, 12:58 AM
  2. Replies: 16
    Last Post: 08-18-2014, 11:05 AM
  3. [SOLVED] Formula Needed to fill multiple cells with "No" when the word "No" is entered into a cell
    By excelteam777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-09-2013, 05:36 PM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  6. Can you create a formula that will count the occurances of a number (say "25")
    By allenratta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2012, 02:49 AM
  7. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1