Conditional range possible?

biostatist

New Member
Joined
Jul 12, 2012
Messages
14
ID
Test Resul
Dose Given
Dose Count
87765
Yes
1
877654
8776520
87765Yes
2
96853
Yes
1
96853Yes
2
968537
9685310
96853Yes
3
435839
4
4358397
435839Yes
1
435839Yes
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! :confused:
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

pplstuff

Well-known Member
Joined
Mar 9, 2012
Messages
951
Assuming ID is A1

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

^^ Drop down.
 

biostatist

New Member
Joined
Jul 12, 2012
Messages
14
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.
 
Last edited:

biostatist

New Member
Joined
Jul 12, 2012
Messages
14
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

Well-known Member
Joined
Mar 9, 2012
Messages
951

ADVERTISEMENT

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 12, 2012
Messages
14

ADVERTISEMENT

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

Well-known Member
Joined
Mar 9, 2012
Messages
951
If every dose is 1... then you don't need a sumif(), because countif() will always add 1.
 

biostatist

New Member
Joined
Jul 12, 2012
Messages
14
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

New Member
Joined
Jul 12, 2012
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top