Counting Cells in Multiple Ranges with Multiple Criteria

[RoniS]

Hi,

I was wondering if anyone could help.

I want to produce a summary table that is based on Excel tab data. Within each tab, members of staff are updating to show whether they have sampled/checked work across a given quarter i.e April to June. Each piece of work is earmarked to be checked within a certain month. Once they have checked/sampled work, they manually put a date into a cell. I want a summary table to show, across a given quarter, a staff member would be 'expecting' a certain number of pieces of work. The next column would then, if they had 'checked' the work it would automatically be reflected in a completed total against the person and the quarter. Likewise, outstanding work would be totalled into the 'outstanding' cell.

From excel tab
Due Date Completed
31/01/2020 31/03/2020
28/07/2020
30/12/2019

Within the summary sheet it would show against this person, they had for the quarter (April to June), 2 pieces to mark, 1 had been completed i.e.

Completed (Q1) Outstanding
1 1

Alternatively, if the summary tab only reflected the number due for a quarter and the number completed i.e. based on those that are showing as blank cells.

I hope this makes sense? I've tried various combos of formulas but it only seems to 'read' the first part, therefore is incorrect.

Any help is appreciated.

thanks

Roni

 

[MrSyyr]

Hi,
I'm afraid this is difficult to understand. Please give a simplified example in a table or pic format.
The following things, amongst others, are unclear to me:
Is there one tab per member of staff?
Do you just want to summarise completed against target for each staff member?
Do you want to check it was completed in the right month?
Is all the data in one workbook?
What columns do you want in the summary table?
MrS

 

[RoniS]

Hi,

Thanks for the reply.

Each staff member has a tab, holding a list of learner's work that is to be checked across a year. Yes, I want to summarise the completed against targeted across a month, quarterly then a year. The month that that work is checked in, isn't as critical, just that it has been checked. Yes, all the data is in one workbook.

Each staff members tab holds learner's details along the lines of:

Expected Initial Sample Date	Expected Initial Sample Month	Actual Initial Sample Date
07/02/2020​	February, 2020

The above is repeated for a mid point sample and then for a final. When a staff member checks the work, they put a date into the 'Actual Sample Date' field, to show that it has been completed.

What I would like then is a summary tab, showing the 19 members of staff, their 'planned/expected' numbers per month/quarter against how many they have completed.

Does that make sense?

Thank you for your help