CODEWITHSUNDEEP
excelexcel-formula
user3520815
user3520815

Reputation: 31

Conditional count over rows over multiple criteria

I want to answer this question: How many patients have had more than 1 scan? Pseudocode:

Countif >1 (Ans = 2. Two patients have 2 or more scans)
    For each Patient ID, count if "scan" nonblank (Ans: a=1, b=2, c=2, d=1)

      A          | B      | C
 01   Patient ID | Scan   | Other data etc
 02   a          |        | 
 03   a          | 1/1/15 |
 04   b          | 2/2/15 |
 05   b          |        |
 06   b          | 3/2/16 | 
 07   c          | 1/3/14 |
 08   d          | 2/1/12 |
 09   c          | 1/7/14 |
 10   d          |        |

This is easy to do for just one patient. I.e., =Countifs(A1:10,"a",B1:10,"<>"). How do you count this for all patients?

Help much appreciated!

Upvotes: 0

Views: 64

Answers (3)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

The formula will need to be entered as an array (once copy and pasted while still in the formula bar hit CTRL+SHIFT+ENTER)

=SUM(--(FREQUENCY(IF(B2:B10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1)>1))

Upvotes: 1

Karthikeyan Vedi
Karthikeyan Vedi

Reputation: 1360

You will require a helper column - refer the formula below

Helper column =IF(AND(COUNTIF($A$2:$A$10,A2)>1,NOT(ISBLANK(B2)) ),A2,"-")

=IF(COUNTIF(C2:C10,"-")>0,SUMPRODUCT(1/COUNTIF(C2:C10,C2:C10))-1,SUMPRODUCT(1/COUNTIF(C2:C10,C2:C10)))

enter image description here

Upvotes: 1

Jack Wire
Jack Wire

Reputation: 721

If you have a sheet with every patient you can add a column and use your formula =Countifs(A1:10,"patient_id",B1:10,"<>") to count the number of scans for each patient and then use the same type of formula to count every sum of scans that are greater than 1.

Patient sheet

      A          | B     
 01   Patient ID | Scans  
 02   a          | =COUNTIFS(scans!A2:A10; A2;scans!B2:B10;"<>")     
 03   b          | 2      
 04   c          | 2      
 05   d          | 1      
 06   e          | 0  
 07   f          | 0

Scan sheet

      A          | B      | C
 01   Patient ID | Scan   | Other data etc
 02   a          |        | 
 03   a          | 1/1/15 |
 04   b          | 2/2/15 |
 05   b          |        |
 06   b          | 3/2/16 | 
 07   c          | 1/3/14 |
 08   d          | 2/1/12 |
 09   c          | 1/7/14 |
 10   d          |        |

Where you want

=COUNTIF(patients!B2:B6;">1")

Upvotes: 0

Related Questions