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

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!

Originally Posted by Richard Buttrey

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:

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

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

Originally Posted by hrlngrv

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

Thanks for everyones help.

Sorted it now.

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

Cheers