Frequency Problem

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...

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

Hi kersplash,

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

Regards

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

I think I have uploaded the sample file.

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

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

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.

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.

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

Hi All,

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

Thanks for the info.