data validation, totaling values based off dropdown lists
 

The situation: I have a list of patient names in one column, a dropdown list
with four text options (for their type of injury) in another, and another
dropdown list with a numerical value of how many administrative hours spent
on them in another dropdown list.

I need a formula that will keep tally of the number of hours spent on a
patient according to their injury. So, in short, I need add the total number
of hours for only those patients who have a specific text item selected in
the first dropdown list.

Thanks to all who reply to these things. I have learned a lot about excel in
just two days thanks to your help.

data validation, totaling values based off dropdown lists
 

I need add the total number of hours for only those patients
who have a specific text item selected in the first dropdown list.

Column B = drop down list of injuries
Column C = drop down list of hours

=SUMPRODUCT(--(B1:B10="injury"),C1:C10)

Replace injury with the actual injury: fracture, laceration, sprain,
whatever.

Better to use a cell to hold the criteria:

E1 = fracture

=SUMPRODUCT(--(B1:B10=E1),C1:C10)


--
Biff
Microsoft Excel MVP


"Jesse Blanchard" wrote in
message ...
The situation: I have a list of patient names in one column, a dropdown
list
with four text options (for their type of injury) in another, and another
dropdown list with a numerical value of how many administrative hours
spent
on them in another dropdown list.

I need a formula that will keep tally of the number of hours spent on a
patient according to their injury. So, in short, I need add the total
number
of hours for only those patients who have a specific text item selected in
the first dropdown list.

Thanks to all who reply to these things. I have learned a lot about excel
in
just two days thanks to your help.

data validation, totaling values based off dropdown lists
 

Suppose you have names in Col A, type of injury in Col B (say four text
values A, B, C, and D) and hours in Col C then enter this wherever you want
to total hours for say type a
=SUMPRODUCT(--((B1:B16) = "A"),C1:C16)
change B16 and C16 to the end of your data range
ALSO pl press CTRL-SHIFT-ENTER together to get this to work

You can do similar formula for other types or have A,B, in a cell and refer
to that cell in place of "A" above

"Jesse Blanchard" wrote:

The situation: I have a list of patient names in one column, a dropdown list
with four text options (for their type of injury) in another, and another
dropdown list with a numerical value of how many administrative hours spent
on them in another dropdown list.

I need a formula that will keep tally of the number of hours spent on a
patient according to their injury. So, in short, I need add the total number
of hours for only those patients who have a specific text item selected in
the first dropdown list.

Thanks to all who reply to these things. I have learned a lot about excel in
just two days thanks to your help.

data validation, totaling values based off dropdown lists
 

press CTRL-SHIFT-ENTER together to get this to work

Not necessary. A normal ENTER will do.

--
Biff
Microsoft Excel MVP


"Sheeloo" wrote in message
...
Suppose you have names in Col A, type of injury in Col B (say four text
values A, B, C, and D) and hours in Col C then enter this wherever you
want
to total hours for say type a
=SUMPRODUCT(--((B1:B16) = "A"),C1:C16)
change B16 and C16 to the end of your data range
ALSO pl press CTRL-SHIFT-ENTER together to get this to work

You can do similar formula for other types or have A,B, in a cell and
refer
to that cell in place of "A" above

"Jesse Blanchard" wrote:

The situation: I have a list of patient names in one column, a dropdown
list
with four text options (for their type of injury) in another, and another
dropdown list with a numerical value of how many administrative hours
spent
on them in another dropdown list.

I need a formula that will keep tally of the number of hours spent on a
patient according to their injury. So, in short, I need add the total
number
of hours for only those patients who have a specific text item selected
in
the first dropdown list.

Thanks to all who reply to these things. I have learned a lot about excel
in
just two days thanks to your help.

data validation, totaling values based off dropdown lists
 

Thanks to all.

"T. Valko" wrote:

press CTRL-SHIFT-ENTER together to get this to work

Not necessary. A normal ENTER will do.

--
Biff
Microsoft Excel MVP


"Sheeloo" wrote in message
...
Suppose you have names in Col A, type of injury in Col B (say four text
values A, B, C, and D) and hours in Col C then enter this wherever you
want
to total hours for say type a
=SUMPRODUCT(--((B1:B16) = "A"),C1:C16)
change B16 and C16 to the end of your data range
ALSO pl press CTRL-SHIFT-ENTER together to get this to work

You can do similar formula for other types or have A,B, in a cell and
refer
to that cell in place of "A" above

"Jesse Blanchard" wrote:

The situation: I have a list of patient names in one column, a dropdown
list
with four text options (for their type of injury) in another, and another
dropdown list with a numerical value of how many administrative hours
spent
on them in another dropdown list.

I need a formula that will keep tally of the number of hours spent on a
patient according to their injury. So, in short, I need add the total
number
of hours for only those patients who have a specific text item selected
in
the first dropdown list.

Thanks to all who reply to these things. I have learned a lot about excel
in
just two days thanks to your help.

data validation, totaling values based off dropdown lists
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Jesse Blanchard" wrote in
message ...
Thanks to all.

"T. Valko" wrote:

press CTRL-SHIFT-ENTER together to get this to work

Not necessary. A normal ENTER will do.

--
Biff
Microsoft Excel MVP


"Sheeloo" wrote in message
...
Suppose you have names in Col A, type of injury in Col B (say four text
values A, B, C, and D) and hours in Col C then enter this wherever you
want
to total hours for say type a
=SUMPRODUCT(--((B1:B16) = "A"),C1:C16)
change B16 and C16 to the end of your data range
ALSO pl press CTRL-SHIFT-ENTER together to get this to work

You can do similar formula for other types or have A,B, in a cell and
refer
to that cell in place of "A" above

"Jesse Blanchard" wrote:

The situation: I have a list of patient names in one column, a
dropdown
list
with four text options (for their type of injury) in another, and
another
dropdown list with a numerical value of how many administrative hours
spent
on them in another dropdown list.

I need a formula that will keep tally of the number of hours spent on
a
patient according to their injury. So, in short, I need add the total
number
of hours for only those patients who have a specific text item
selected
in
the first dropdown list.

Thanks to all who reply to these things. I have learned a lot about
excel
in
just two days thanks to your help.