Records and Rows If Statement Question / Help :)

[colerainescotty]

Hi folks, I have a simple excel spreadsheet that I am creating to get better at Excel and formulas for my IT course. I have a spreadsheet with some dummy data related to hospital procedures.

At the moment there are 6 doctors who carry out a procedure. The doctors in theory have to be audited and a report sheet produced. At the moment all of the data in a row relates to a particular doctor. There are various pieces of data I wish to collate for a particular doctor.

Firstly if possible I would like to be able to (for the report) say "Find all rows with Dr. A and calculate total dose of Drug A given to patients"


Here is an example of what I am trying to achieve

Excel Sheet

A B C D
1 Doctor Drug Dose
2 Dr. A 1mg
3 Dr. B 2mg
4 Dr. A 4mg

Is there a formula for me to be able to say in another "reporting" sheet (if row contains "Dr A" then total "drug dose")

If you need anymore information please do not hesitate to reply and I will be happy to answer.

Thanks so much in advance and I may have more questions soon

 

[jtakw]

Hi,

If you put "mg" in the header rather than within the cells, you can do simply this:

Excel 2010
	A	B
1	Doctor	Drug Dose mg
2	Dr. A	1
3	Dr. B	2
4	Dr. A	4
Sheet2

Excel 2010
	A	B
1	Doctor	Total Drug Dose mg
2	Dr. A	5
3	Dr. B	2
reporting

Cell Formulas
Range	Formula
B2	=SUMIF(Sheet2!A$2:A$4,A2,Sheet2!B$2:B$4)

reporting sheet B2 formula copied down.

 

[colerainescotty]

Hi Thanks for this. This is a good solution if I know where the data is on Sheet 2. However say that there are around 8 records input daily with different doctors. At the end of the month for the report is there a way to automatically do this. E.g for the excel sheet to go and find the amount of dosage total for "Dr. A", then "Dr. B" etc without someone having to manually input and select data?

If there are 100 records or so per month this would make the reporting easier. There are more things I need to do but If I could crack this one I would be flying

I am beginning to wonder if a database would be better for this project?

Hope to hear from you soon and thanks for the help

 

[jtakw]

I'm not sure I understand what you mean.

Are you saying you don't know where the data is, in Sheet 2, meaning you don't know what Column or what Rows?

Or, are you saying the number of rows keeps growing?

If it's the latter, you can extend the range in the formula to beyond what you anticipate you need, like:

=SUMIF(Sheet2!A$2:A$400,A2,Sheet2!B$2:B$400)

 

[colerainescotty]

Hi sorry yes the list will keep growing and there may be different doctors as the list grows. But if I want to report for only certain doctors individually say Dr. A, is there a way to get it to automatically obtain some of the other data in the row if the row relates to Dr A for example. So say I wanted to know the total dose given by Dr.A is there a way to get this from the whole set of records without having to manually look down the list and select all of the doctor A's because there will be other doctors other than Dr.A in there too. Basically I would like for the list to be able to grow and daily records added but the report to populate automatically as the list grows.

Thanks in advance and I hope I have made it clear. If not sorry and please ask away and I can clarify.

 

[jtakw]

I still don't know what your concern is, if I understand what you're saying, that's what the formula I provided does:

Excel 2010
	A	B
1	Doctor	Drug Dose mg
2	Dr. A	1
3	Dr. B	2
4	Dr. A	4
5	Dr. A	2
6	Dr. A	5
7	Dr. C	7
8	Dr. C	6
9	Dr. C	9
10	Dr. C	4
11	Dr. Who	3
12	Dr. Who	5
13	Dr. A	6
14	Dr. C	8
15	Dr. Watson	1
16	Dr. Watson	2
17	Dr. Watson	5
18	Dr. A	4
19	Dr. Who	6
20	Dr. B	9
21	Dr. Pepper	7
22	Dr. Pepper	8
23	Dr. A	2
Sheet2

Excel 2010
	A	B
1	Doctor	Total Drug Dose mg
2	Dr. A	24
3	Dr. B	11
4	Dr. C	34
5	Dr. Pepper	15
6	Dr. Watson	8
7	Dr. Who	14
reporting

Cell Formulas
Range	Formula
B2	=SUMIF(Sheet2!A$2:A$400,A2,Sheet2!B$2:B$400)

Look here: https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b

 

[colerainescotty]

Oh I read the article there and get you now. That is brilliant. Thank you so much and sorry for the slow pickup. Must have been one of those nights lol

 

[jtakw]

That happens sometimes. You're welcome.

 

[colerainescotty]

Thanks Jtakw,

This worked a treat

On another note do you know how I could do the following 2 things: I would be ever so grateful

1) Return % of patients that received a certain drug by a certain Dr?

2) Return average dose

E.g Total Midazalam given divided by the number of patients that received it. I will call the result "Ave Dose" Also I was trying to have it unique to each doctor e.g Dr. A, Dr. B for the reporting page. I only want the excel to count the dose where patients actually received it. If a patient was not given it for any reason then I wish to leave it out of the calculation. Please see the below to show what I mean.

Doctor	Dose mg		Doctor	Average Dose	% Given
A	2		Dr. A	2.50	50.00%
B	3		Dr. B	3.00	100.00%
C	1		Dr. C	1.00	100.00%
A	1
A	0
A	0

<tbody>
</tbody>

I was hoping there may be a formula like the sumif that may help. I tried the sumif formula with the countif but to no avail. I hope to hear from you soon. If anything is not clear please let me know and I will clarify. Thanks so much for the excellent help

I meant to add that the list of data will keep growing and so can change daily so if it is possible to have a formula like the sumif one you made that would be amazing as it just automatically updates the results as new data is added making the reporting really easy.

 

[jtakw]

Please clarify how you came up with 2.50 as "Average Dose" for "Dr. A" in your sample above.
Dr. A shows up 4 times in the far left column, administered 2 doses (2, and 1), the other 2 times, Dr. A did not (0, and 0). Then also, how is the "% Given" derived for Dr. A?