Frequency Problem

[Sandgroper71]

Hi All,

I have been having the following problem and need I say a lot of help:

I have a excel spreadsheet that tracks works via sheets:

Date Range 01/09/2017 to 30/09/2017

Sheet No. Name Date Inputted Hours Inputted Date Submitted Hours Submitted Date Approved Approved Hours
1234 Joe 24-08-17 10 24-08-17 10 01-09-17 10
1234 Karen 30-09-17 10 30-09-17 10 02-09-17 10
1526 Mark 01-09-17 10 01-09-17 10 15-09-17 8
1247 Peter 03-09-17 12 03-09-17 12 08-09-17 10
1526 Cathy 15-09-17 10 15-09-17 10 23-09-17 10
1841 Chloe 23-09-17 10 23-09-17 10
1573 Fred 30-09-17 12

I need to produce reports for each month that shows:
How many sheets for the month,
How many sheets have been inputted,
How many sheets have been approved.

I have been trying but it seems not to capture the correct amounts:
=SUM(--(FREQUENCY(IF($C:$C>=$B$1,IF($C:$C<=$D$1,$A:$A)),$A:$A)>0))
=SUM(--(FREQUENCY(IF($C:$C>=$B$1,IF($C:$C<=$D$1,$A:$A,IF(E:E>=B1,E:E<=D1))),$A:$A)>0))
=SUM(--(FREQUENCY(IF($C:$C>=$B$1,IF($C:$C<=$D$1,$A:$A,IF(G:G>=B1,G:G<=D1))),$A:$A)>0))

Please help...

[kersplash]

Can you attach a sample workbook? Looks like a pivot table may work for this.

[Sandgroper71]

Hi kersplash,

I tried but will not allow me to attach the sample for some reason.

Regards

[kersplash]

Click "Go Advanced" then "Manage Attachments" then attach and upload.

[Sandgroper71]

I think I have uploaded the sample file.

[kersplash]

Have a look at the attached pivot tables to see if this answers your question.

[Sandgroper71]

the pivot tables are not exactly what I am chasing.

this formula will be a part of a bigger picture.

I have over 4 years of data to show in a report.

It would be great if I could do a formula that counts the number of sheets each month that meets:
inputted in the month I select, has been submitted and has ben approved

[kersplash]

That's fine a pivot table will show you the results for 4 years worth of data, you just change the data source of the pivot to match the range of data.

If you want to use formulas then that will work too, just not my forte.

[Sandgroper71]

Yes I need to use a formula because of the duplicates see the worksheet has numerous names against each sheet so I want to capture the no. of sheets not rows. for auditing purposes.

Hopefully someone out there will know.

thanks, anyway for trying.

[FlameRetired]

These formulas are array entered.

It appears you are familiar with how to commit those Ctrl + Shift + Enter.

In J1:J2 the date range to test.

Array entered in K3.

=SUM(IF(FREQUENCY(IF(($C$2:$C$8>=$J$1)*($C$2:$C$8<=$J$2),$A$2:$A$8),$A$2:$A$8),1))

Same in K4.

=SUM(IF(FREQUENCY(IF(($E$2:$E$8>=$J$1)*($E$2:$E$8<=$J$2),$A$2:$A$8),$A$2:$A$8),1))

In K5

=SUM(IF(FREQUENCY(IF(($G$2:$G$8>=$J$1)*($G$2:$G$8<=$J$2),$A$2:$A$8),$A$2:$A$8),1))

	J	K	L
1	9/1/2017
2	9/30/2017
3	Input	5	In K3 :{=SUM(IF(FREQUENCY(IF(($C$2:$C$8>=$J$1)*($C$2:$C$8<=$J$2),$A$2:$A$8),$A$2:$A$8),1))}
4	Submitted	4	In K4 :{=SUM(IF(FREQUENCY(IF(($E$2:$E$8>=$J$1)*($E$2:$E$8<=$J$2),$A$2:$A$8),$A$2:$A$8),1))}
5	Approved	3	In K5 :{=SUM(IF(FREQUENCY(IF(($G$2:$G$8>=$J$1)*($G$2:$G$8<=$J$2),$A$2:$A$8),$A$2:$A$8),1))}

[Sandgroper71]

Hi Dave,

that's nearly what I am looking for, the only thing is that Submitted and Approved may be a different month.

the report I need to show is going to be a live document in the way that Submitted and Approved will be shown the difference each month.

ie. Sept 2017 Inputted 117, out of 117, 97 were submitted and out of the 117, 68 were approved and when I print the report in Oct 17 it has a running total like 117 out of the 117 inputted 112 have been submitted & 97 have been approved.

I hope this helps.

That's why I want the formula.

really appreciate your time on this I have been at it for 2 days.

Regards,

Mark

[Sandgroper71]

Hi All,

Thanks for your help I have worked it out see attached spreadsheet.
the formula's needed another date to reflect.

[FlameRetired]

Glad to see you worked it out, and thank you for further explanation and upload.

Of note:
You do well to avoid whole column references when applying math operations. It forces Excel to do over 1 million unnecessary calculations in each call. This slows performance.

A better approach is to make the ranges a little larger than anticipated ... i.e. if you anticipate using @ 1000 rows make the range 1500.

Another approach (better in my opinion) would be to make those Dynamic Named Ranges and then reference those ranges by name. If you are not familiar with those they dynamically resize to fit the data.

Good work.

[Sandgroper71]

Thanks for the info.

[FlameRetired]

You're welcome. Thanks for the feedback, rep and marking your thread Solved.