Forum Discussion
Ignore duplicates with multiple criteria and between 2 dates
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 |
=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),"" ) ) )
9 Replies
- If you can post a file it would be great
- Have now done so.
=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),"" ) ) )
=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?
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.
=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"))
