I've collected some vaccine administration data that I want to analyze for unique patients in different age groups based on their registration date.
So far I have used a Sum - IF - Frequency equation to determine the number of unique patients depending on the date the patient was registered, however, I can't figure out how to adjust the formula to add in patient age to further dissect the data.
Here is the formula used for # of unique patients based on the date the patient was registered which would coordinate with I2 of the sample table below: Ctl+Shift+Enter: =SUM(IF(FREQUENCY(IF(1-(Table2[Patient Full Name]=""),IF(Table2[Registration Date]>=1,IF(Table2[Registration Date]<$P$2,MATCH(Table2[Patient Full Name],Table2[Patient Full Name],0)))),ROW(Table2[Patient Full Name])-ROW($A$2:$A$2)+1),1))
In this example I need to be able to count the number of unique patients in each age category that correspond with the registration date. This is just a small sample of fake patient data, real sheet is over 700 lines and growing, but these are the main columns used from the master data.
Thank you for looking at my issue!
So far I have used a Sum - IF - Frequency equation to determine the number of unique patients depending on the date the patient was registered, however, I can't figure out how to adjust the formula to add in patient age to further dissect the data.
Here is the formula used for # of unique patients based on the date the patient was registered which would coordinate with I2 of the sample table below: Ctl+Shift+Enter: =SUM(IF(FREQUENCY(IF(1-(Table2[Patient Full Name]=""),IF(Table2[Registration Date]>=1,IF(Table2[Registration Date]<$P$2,MATCH(Table2[Patient Full Name],Table2[Patient Full Name],0)))),ROW(Table2[Patient Full Name])-ROW($A$2:$A$2)+1),1))
| Vaccine Stats 2021 -11-08-2021.xlsx | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | Patient Full Name | Dispensed Item | Patient Age | Registration Date | Patient Registered | # of Unique Patients | # of Vaccines | Unique Patients ≤11 | Unique Patients 12 to 17 years | Unique Patients 18 to 49 Years | Unique Patients 50 to 64 Years | Unique Patients >65 years | Dates Used for Breaks | |||||
| 2 | Smith, Joe | Fluad Quad 2021-2022 Syringe | 76 | 8/2/2012 | < 9/1/2021 | 3 | 5 | 9/1/2021 | ||||||||||
| 3 | Smith, Joe | Shingrix Vial Kit | 76 | 8/2/2012 | 9/1/2021 to 9/30/2021 | 3 | 4 | 10/1/2021 | ||||||||||
| 4 | Franks, Bob | Afluria Quad 2021-22 (3 Year Up) 0.5 Ml Syringe | 28 | 12/1/2021 | 10/1/2021 to 10/31/2021 | 4 | 5 | 11/1/2021 | ||||||||||
| 5 | Dolan, Julie | Shingrix Vial Kit | 52 | 10/22/2021 | 11/1/2021 to 11/30/2021 | 2 | 2 | 12/1/2021 | ||||||||||
| 6 | Dillard, Billy | Pfizer-Biontech Covid-19 Vaccine Vial (eua) | 14 | 1/1/2015 | 12/1/2021 to 12/31/2021 | 1 | 1 | 1/1/2022 | ||||||||||
| 7 | Doe, John | Pfizer-Biontech Covid-19 Vaccine Vial (eua) | 29 | 2/2/2018 | ||||||||||||||
| 8 | Doe, John | Afluria Quad 2021-22 (3 Year Up) 0.5 Ml Syringe | 29 | 2/2/2018 | ||||||||||||||
| 9 | Smith, Sherry | Pfizer-Biontech Covid-19 Vaccine Vial (eua) | 64 | 9/10/2021 | ||||||||||||||
| 10 | Winston, Vanessa | Afluria Quad 2021-22 (3 Year Up) 0.5 Ml Syringe | 52 | 9/21/2021 | ||||||||||||||
| 11 | Blake, William | Afluria Quad 2021-22 (3 Year Up) 0.5 Ml Syringe | 9 | 10/2/2021 | ||||||||||||||
| 12 | Doe, Jennifer | Afluria Quad 2021-22 (3 Year Up) 0.5 Ml Syringe | 7 | 11/12/2021 | ||||||||||||||
| 13 | Swindle, Frank | Shingrix Vial Kit | 53 | 11/15/2021 | ||||||||||||||
| 14 | Rubble, Barney | Pfizer-Biontech Covid-19 Vaccine Vial (eua) | 49 | 10/29/2021 | ||||||||||||||
| 15 | Rubble, Betty | Afluria Quad 2021-22 (3 Year Up) 0.5 Ml Syringe | 48 | 10/29/2021 | ||||||||||||||
| 16 | Rubble, Betty | Pfizer-Biontech Covid-19 Vaccine Vial (eua) | 48 | 10/29/2021 | ||||||||||||||
| 17 | Bacon, Chris P. | Fluad Quad 2021-2022 Syringe | 66 | 9/8/2021 | ||||||||||||||
| 18 | Bacon, Chris P. | Pfizer-Biontech Covid-19 Vaccine Vial (eua) | 66 | 9/8/2021 | ||||||||||||||
Sheet1 | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I2 | I2 | =SUM(IF(FREQUENCY(IF(1-(Table2[Patient Full Name]=""),IF(Table2[Registration Date]>=1,IF(Table2[Registration Date]<$P$2,MATCH(Table2[Patient Full Name],Table2[Patient Full Name],0)))),ROW(Table2[Patient Full Name])-ROW($A$2:$A$2)+1),1)) |
| J2 | J2 | =COUNTIFS(Table2[Registration Date],"<"&P2,Table2[Registration Date],">="&1) |
| I3 | I3 | =SUM(IF(FREQUENCY(IF(1-(Table2[Patient Full Name]=""),IF(Table2[Registration Date]>=$P$2,IF(Table2[Registration Date]<$P$3,MATCH(Table2[Patient Full Name],Table2[Patient Full Name],0)))),ROW(Table2[Patient Full Name])-ROW($A$2:$A$2)+1),1)) |
| J3:J6 | J3 | =COUNTIFS(Table2[Registration Date],"<"&P3,Table2[Registration Date],">="&P2) |
| I4 | I4 | =SUM(IF(FREQUENCY(IF(1-(Table2[Patient Full Name]=""),IF(Table2[Registration Date]>=$P$3,IF(Table2[Registration Date]<$P$4,MATCH(Table2[Patient Full Name],Table2[Patient Full Name],0)))),ROW(Table2[Patient Full Name])-ROW($A$2:$A$2)+1),1)) |
| I5 | I5 | =SUM(IF(FREQUENCY(IF(1-(Table2[Patient Full Name]=""),IF(Table2[Registration Date]>=$P$4,IF(Table2[Registration Date]<$P$5,MATCH(Table2[Patient Full Name],Table2[Patient Full Name],0)))),ROW(Table2[Patient Full Name])-ROW($A$2:$A$2)+1),1)) |
| I6 | I6 | =SUM(IF(FREQUENCY(IF(1-(Table2[Patient Full Name]=""),IF(Table2[Registration Date]>=$P$5,IF(Table2[Registration Date]<$P$6,MATCH(Table2[Patient Full Name],Table2[Patient Full Name],0)))),ROW(Table2[Patient Full Name])-ROW($A$2:$A$2)+1),1)) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
In this example I need to be able to count the number of unique patients in each age category that correspond with the registration date. This is just a small sample of fake patient data, real sheet is over 700 lines and growing, but these are the main columns used from the master data.
Thank you for looking at my issue!
