+ Reply to Thread
Results 1 to 10 of 10

Not sure if nested COUNTIF function or macro needed

  1. #1
    Registered User
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    89

    Not sure if nested COUNTIF function or macro needed

    Hey,



    I have a workbook with 365 sheets, as they are 1 for each day of the year, as youve probably guessed.

    C1 in each sheet contains the day of the week, and each sheet contains the amount of clients we delivered to that day.

    I am now trying to see which day of the week is the busiest, so i have the below formula for counting the occurrences of each day.



    =SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$1:$A$365&"'!C1"),G2))



    G2 being the master sheets day of the week, so the next one, Tuesday, is in G3 etc.



    The A Column is the name of every sheet as i read that that was the best way to do that part.



    I now need to count how many Sales order numbers appear in each worksheet that contains each day of the week, the SO numbers will start in C4 and end at the next blank row.



    Will this be a nested countif and if so, can someone help me put that together.



    If not, a macro that does the same job would be much appreciated.



    Cheers

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    43,823

    Re: Not sure if nested COUNTIF function or macro needed

    Please attach a sample workbook.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,306

    Re: Not sure if nested COUNTIF function or macro needed

    Hi,

    Usually the simplest way to do this is have the count you require on each daily sheet in the SAME cell on each sheet. Then you can use a formula like the following. It's also common to insert two blank sheets, one as the first tab and the other the last tab with all the days in between.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    where 'First' & 'Last' are the two blank sheets and G2 the cell containing your formula.

    It's not clear whether that formula you show is on each sheet or on a master summary sheet. However upload a daily sheet so that we can advise further.

    That said, keeping your daily data on 365 sheets is really not the best approach. You ae complicating any analysis you want by doing so. All data should be on a single sheet and with an extra initial column that contains the date of each record. A simple macro could be written to create that single sheet database and I would urge you to seriously consider that.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    89

    Re: Not sure if nested COUNTIF function or macro needed

    See attached.

    The names and other info is the same throughout for speed, but obviously theyll be different

    And the number or rows is different, but same again, i copied and pasted the table for speed.

    Thanks for your help!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    89

    Re: Not sure if nested COUNTIF function or macro needed

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Usually the simplest way to do this is have the count you require on each daily sheet in the SAME cell on each sheet. Then you can use a formula like the following. It's also common to insert two blank sheets, one as the first tab and the other the last tab with all the days in between.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    where 'First' & 'Last' are the two blank sheets and G2 the cell containing your formula.

    It's not clear whether that formula you show is on each sheet or on a master summary sheet. However upload a daily sheet so that we can advise further.

    That said, keeping your daily data on 365 sheets is really not the best approach. You ae complicating any analysis you want by doing so. All data should be on a single sheet and with an extra initial column that contains the date of each record. A simple macro could be written to create that single sheet database and I would urge you to seriously consider that.
    Hey Richard,

    I keep saying that to my colleagues. The data is theirs, the solution to issues like this is up to me to find.

    Ive already started this years in a similar fashion to you recommendation.

    Cheers

  6. #6
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,117

    Re: Not sure if nested COUNTIF function or macro needed

    Picky 1st: 2020 has 366 days because it's a leap year.

    If you're going to have a lot of these formulas, you may want to avoid INDIRECT. If you have a list of all worksheet names in a summary worksheet, then put them in A3:A368. If you need the cell C1 values from each of these worksheets, enter C1 as text in cell B1 of this summary worksheet. Then

    B3: ="='"&$A3&"'!"&B$1

    That should produce text which looks like a formula. Fill B3 down into B4:B368. Select B3:B368, copy, and paste special as values. That converts formulas returning text which looks like formulas into text constants which look like formulas. Then use Find and Replace to change all = to =, yes, replacing the equal sign with itself. That effectively enters all these text constants as formulas. Then you could just use

    =COUNTIF($B$3:$B$368,G2)

    If you need other cells from each worksheet, you can use the same approach with them in other columns from C right.

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,306

    Re: Not sure if nested COUNTIF function or macro needed

    In a cell on row 1 of the master sheet copied down

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


    It assumes no extraneous stuff like text, comments etc. is added in column E

    So since it's up to you to find the solution are you happy to have a master data sheet added to the data whilst unnecessarily keeping all 365 sheets for the benefit of the dinosaur users who are constraining you.

    I'm intrigued to understand how many contributors you actually have, the practical operation of your system and who adds a new sheet each day.

  8. #8
    Registered User
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    89

    Re: Not sure if nested COUNTIF function or macro needed

    This was for last year, hence the reason i have 365 days worth of data.

    Also, if im not mistaken, this will count the number of times each day appears in the workbook.

    I want the count for cell C4 down on each occuring day, as each row C4, C5, C6 etc contains an Sales order number, so i want to see on a Monday, how many sales orders there are for the year.

    So if worksheet 1 has a count of 10 from C4 - C13 and is a monday and worksheet 10 is also a Monday and has a count of 5 from C4 - C8, on the master sheet it will say Monday = 14

  9. #9
    Registered User
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    89

    Re: Not sure if nested COUNTIF function or macro needed

    Quote Originally Posted by hrlngrv View Post
    Picky 1st: 2020 has 366 days because it's a leap year.

    If you're going to have a lot of these formulas, you may want to avoid INDIRECT. If you have a list of all worksheet names in a summary worksheet, then put them in A3:A368. If you need the cell C1 values from each of these worksheets, enter C1 as text in cell B1 of this summary worksheet. Then

    B3: ="='"&$A3&"'!"&B$1

    That should produce text which looks like a formula. Fill B3 down into B4:B368. Select B3:B368, copy, and paste special as values. That converts formulas returning text which looks like formulas into text constants which look like formulas. Then use Find and Replace to change all = to =, yes, replacing the equal sign with itself. That effectively enters all these text constants as formulas. Then you could just use

    =COUNTIF($B$3:$B$368,G2)

    If you need other cells from each worksheet, you can use the same approach with them in other columns from C right.
    This was for last year, hence the reason i have 365 days worth of data.

    Also, if im not mistaken, this will count the number of times each day appears in the workbook.

    I want the count for cell C4 down on each occuring day, as each row C4, C5, C6 etc contains an Sales order number, so i want to see on a Monday, how many sales orders there are for the year.

    So if worksheet 1 has a count of 10 from C4 - C13 and is a monday and worksheet 10 is also a Monday and has a count of 5 from C4 - C8, on the master sheet it will say Monday = 14

  10. #10
    Registered User
    Join Date
    12-14-2017
    Location
    birmingham, england
    MS-Off Ver
    2016
    Posts
    89

    Re: Not sure if nested COUNTIF function or macro needed

    Thanks for everyones help.

    Sorted it now.

    Used Richard Buttreys =COUNTIF(INDIRECT("'"&A1&"'!E:E"),"<>")-1 along with hrlngrv initial solution.

    Cheers

+ 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. Replies: 6
    Last Post: 11-07-2018, 02:10 AM
  2. Nested IF function needed?
    By Sareno in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-24-2016, 11:40 AM
  3. [SOLVED] Help needed for a nested IF function
    By icubud in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-15-2016, 11:59 AM
  4. COUNTIF/COUNTIFS function + nested user-defined function
    By shamjamali in forum Excel General
    Replies: 1
    Last Post: 05-12-2015, 09:12 PM
  5. [SOLVED] Nested COUNTIF within IF Function
    By shaunguyver in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-09-2015, 10:47 AM
  6. [SOLVED] Nested IF function needed
    By Petsi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-20-2014, 01:46 PM
  7. Nested IF function (urgent help needed)
    By HJalal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-28-2013, 11:59 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