[h=2]Looking for formula to count very specific scenario[/h]
I have a report that i need for the government;
this is a federal low income medical office that has patients coming in thru the year for visits. each visit has a unique visit number in my excel table. A visit may have only one item charged (a procedure) or many (Usually no more than 4 or 6). Each procedure can have up to four diagnoses assigned to it.
So i need to count for example only "Visits" that have a predefined procedure codes; for example codes 99214 or 99212 or 99211; if a visit does not contain one of these values, i should not count it in the report. So a line on the report might ask for # of visits that had a diagnosis of 345.22; so in the example below, the answer would be only 1, only Jane (and count would be 1, because i need to count the number of visits, not procedures).
Or the report might ask for # of visits that had diagnosis code 345.22 or 333.33 (which in this simple data below would be 2, (Jane and Lisa)).
I cant seem to figure out the formula for only counting the UNIQUE NUMBER OF VISITS for a "range" of criteria. Can anyone help?
I can use countifs(D1:D10, "=345.22") for counting all the diagnoses in that column, but i have to select it ONLY if it contains procedure code (99214 or 99212 or 99211) in the procedure code for that visit; AND i can only count it once per visit (in example below JANE has 345.22 on two different lines for one visit, so i can only count it once per visit #)
Thanks in advance.
A B C D E F G
patient uniquevistnumber procedure diag1 diag2 diag3 diag4
Jane 11111 99213 493.2 345.22 333.33 V38.1
Jane 11111 11101 345.22
Bob 22222 99211 555.1 654.1 987.22
Bob 22222 25112 456.78
Mary 33333 99213 493.2 321
Frank 44444 12345 987.65
Lisa 55555 99214 654.32 852.22 333.33 963.12
Lisa 55555 T11023 654.32
Lisa 55555 45678 852.22
this is a federal low income medical office that has patients coming in thru the year for visits. each visit has a unique visit number in my excel table. A visit may have only one item charged (a procedure) or many (Usually no more than 4 or 6). Each procedure can have up to four diagnoses assigned to it.
So i need to count for example only "Visits" that have a predefined procedure codes; for example codes 99214 or 99212 or 99211; if a visit does not contain one of these values, i should not count it in the report. So a line on the report might ask for # of visits that had a diagnosis of 345.22; so in the example below, the answer would be only 1, only Jane (and count would be 1, because i need to count the number of visits, not procedures).
Or the report might ask for # of visits that had diagnosis code 345.22 or 333.33 (which in this simple data below would be 2, (Jane and Lisa)).
I cant seem to figure out the formula for only counting the UNIQUE NUMBER OF VISITS for a "range" of criteria. Can anyone help?
I can use countifs(D1:D10, "=345.22") for counting all the diagnoses in that column, but i have to select it ONLY if it contains procedure code (99214 or 99212 or 99211) in the procedure code for that visit; AND i can only count it once per visit (in example below JANE has 345.22 on two different lines for one visit, so i can only count it once per visit #)
Thanks in advance.
A B C D E F G
patient uniquevistnumber procedure diag1 diag2 diag3 diag4
Jane 11111 99213 493.2 345.22 333.33 V38.1
Jane 11111 11101 345.22
Bob 22222 99211 555.1 654.1 987.22
Bob 22222 25112 456.78
Mary 33333 99213 493.2 321
Frank 44444 12345 987.65
Lisa 55555 99214 654.32 852.22 333.33 963.12
Lisa 55555 T11023 654.32
Lisa 55555 45678 852.22
