Nov 12 2021 01:27 AM - edited Nov 12 2021 02:51 AM
Hi all
I'm doing a training db and need to specify how many learners completed specific, accredited courses between 2 dates that are on a second sheet.
I have a list of names in Column A (some duplicated), Accredited (Yes or No) in Column D, Funding (A.C.T, Legacy, Zero Cost) in Column F, End Date in Column J, and Status (Completed, In Progress, TBC, etc.) in Column L - all on sheet 1 shown below.
The stats are on sheet 2 including the between dates which change according to the stats required. Date 1 in cell A1, date 2 in cell A2.
I need to state how many students completed accredited courses that weren't funded by Legacy, between the two dates so that the figures will update on change of dates.
Can you help? Thank you in advance.
Jules
Sheet 1
Name | Accredited? | Funding | End Date | Status |
Nother, Anthony | Yes | Legacy | 26/05/2021 | Completed |
Bloggs, Joe | Yes | Legacy | 26/05/2021 | Completed |
Person, James | No | Legacy | 01/07/2021 | Completed |
Peartree, Patty | No | Legacy | 01/07/2021 | Completed |
Nother, Anthony | No | Legacy | 01/07/2021 | Completed |
Lollipop, Daisy | No | Legacy | 01/07/2021 | Completed |
Dearheart, A | No | Legacy | 01/07/2021 | Completed |
Vimes, Sam | No | Legacy | 01/07/2021 | Completed |
Door, Bill | No | Legacy | 01/07/2021 | Completed |
Person, James | Yes | Legacy | 06/07/2021 | Completed |
Lollipop, Daisy | Yes | Legacy | 06/07/2021 | Completed |
Door, Bill | Yes | Legacy | 06/07/2021 | Completed |
Vimes, Sam | Yes | Legacy | 13/07/2021 | Completed |
Bloggs, Joe | Yes | Legacy | 13/07/2021 | Completed |
Person, James | Yes | A.C.T. | 21/07/2021 | Completed |
Ogg, Nanny | Yes | A.C.T. | 22/07/2021 | Completed |
Peartree, Patty | Yes | Legacy | 28/07/2021 | Completed |
Nother, Anthony | Yes | Legacy | 28/07/2021 | Completed |
Lollipop, Daisy | Yes | Legacy | 28/07/2021 | Failed Course |
Dearheart, A | Yes | Legacy | 28/07/2021 | Completed |
Evans, Christopher | Yes | Zero Cost | 29/07/2021 | Waiting to Start |
Peartree, Patty | No | A.C.T. | 23/08/2021 | In Progress |
Peartree, Patty | Yes | Zero Cost | 30/09/2021 | Completed |
Baird, Logie | Yes | A.C.T. | 30/09/2021 | In Progress |
Nother, Anthony | No | A.C.T. | 05/10/2021 | Completed |
Nother, Anthony | No | A.C.T. | 11/10/2021 | Completed |
Nov 12 2021 01:50 AM
=COUNTIFS('Sheet 1'!J2:J27,">="&'Sheet 2'!A1,'Sheet 1'!J2:J27,"<="&'Sheet 2'!A2,'Sheet 1'!D2:D27,"Yes",'Sheet 1'!L2:L27,"Completed",'Sheet 1'!I2:I27,"<>Legacy")
Is this what you are looking for?
Nov 12 2021 01:58 AM
Nov 12 2021 02:30 AM
No. That gives me what I can already get - which is great but I also need it to filter out duplicates of names so that each person is only counted once no matter how many times they completed these specific-type courses within the timeframe.
Nov 12 2021 02:51 AM
Nov 12 2021 03:47 AM - edited Nov 12 2021 04:30 AM
Solution
=COUNTA(
UNIQUE(
FILTER('Training DB'!A2:A27,
('Training DB'!B2:B27="Yes")*
('Training DB'!C2:C27<>"Legacy")*
('Training DB'!G2:G27="Completed")*
('Training DB'!F2:F27>=A1)*
('Training DB'!F2:F27<=A2),""
)
)
)
Nov 12 2021 03:53 AM
=SUMPRODUCT((COUNTIF('Training DB'!A2:A27,'Training DB'!A2:A27)=1)*('Training DB'!B2:B27="Yes")*('Training DB'!C2:C27<>"Legacy")*('Training DB'!G2:G27="Completed"))
Nov 12 2021 03:56 AM
Nov 12 2021 04:03 AM
Nov 12 2021 04:16 AM
Nov 12 2021 03:47 AM - edited Nov 12 2021 04:30 AM
Solution
=COUNTA(
UNIQUE(
FILTER('Training DB'!A2:A27,
('Training DB'!B2:B27="Yes")*
('Training DB'!C2:C27<>"Legacy")*
('Training DB'!G2:G27="Completed")*
('Training DB'!F2:F27>=A1)*
('Training DB'!F2:F27<=A2),""
)
)
)