Conditional range possible?

[biostatist]

ID	Test Resul	Dose Given	Dose Count
87765		Yes	1
87765	4
87765	20
87765		Yes	2
96853		Yes	1
96853		Yes	2
96853	7
96853	10
96853		Yes	3
435839	4
435839	7
435839		Yes	1
435839		Yes	2

<tbody>
</tbody>

For each instance of "Yes" in the "Dose Given?" field, I need a count in the "Dose #" field of which dose that was for the particular ID. I filled it in how I want it, but I cannot come up with a formula that will conditionally count or sum only back to where the ID changes. Either a creatively nested "IF" or some kind of conditional formula seems to be the way to go, but I don't know if the latter is possible. The purpose of the count is for analysis of the appropriateness of the dose based on the last test result and a protocol, if that helps. Thanks, anybody!

 

[pplstuff]

Assuming ID is A1

D2: =if(c2="Yes",countif($a$2:a2,a2),"")

^^ Drop down.

 

[biostatist]

Wait, sorry. I actually tried that one early on. It counts the number of instances that the particular ID and displays the count only when the "Dose" field says yes, but it does not count the actual doses.

 

[biostatist]

I tried to modify this with some IF conditions, but I could never get it to count the number of doses according to the ID. I can count the ID instances, and I can count total doses for the sheet, but not total doses per ID. I need to do this not using a pivot table to make further formula work possible. The sheet I'm using is about 40000 records.

 

[pplstuff]

Sounds like you just want a sumif() or sumifs(). What column are the # of Doses?

 

[Rick Rothstein]

Wait, sorry. I actually tried that one early on. It counts the number of instances that the particular ID and displays the count only when the "Dose" field says yes, but it does not count the actual doses.

What exactly do you mean by "count the actual doses" then? Are you saying the numbers you show in the "Dose Count" column are not the numbers you are trying to produce? If that is the case, then show us the actual numbers you want to see for the sample data you posted.

 

[biostatist]

The Dose # column in the OP shows the numbers I want to produce. I just need a formula that will do that over 40000 rows, about 8000 unique patients. Maybe I should add another field with a "1" for each dose given, and try to produce a SUMIF formula like pplstuff suggested?

 

[pplstuff]

If every dose is 1... then you don't need a sumif(), because countif() will always add 1.

 

[biostatist]

I thought that, too, as I've used countif() a lot more, but I can't get either of them to give me a running count of dose with the range of the patient ID as the range of the count of the doses given.

 

[biostatist]

I figured it out:

=IF(C2="","",COUNTIFS($A$2:A2,A2,$C$2:C2,"Yes"))

It's a running subtotal based on criteria; very useful for what I do.