select cell value based on value in adjacent cell
 

I'm creating a timesheet and running into some trouble calculating total time
usage.
Here's the setup:
I have a column P in which conditional data is entered (e.g., if a person
used Holiday accrual time, they enter code "60"; this field may or may not be
populated, and may use one of 3-4 different codes). The cells in this
column are not contiguous, but broken up by merged cells.
The next coloumn, Q, holds the actual hours of accruals used (general
formatting). The cells in this column are not contiguous, but broken up by
merged cells, matching column P.
Way down at the bottom of the timesheet, I need to calculate the number of
hours of accruals used based on what code is entered in column P. So here's
what I need to figure out how to calculate:
If any cell in column P (P4-43, skipping the merged cells) holds the code
"60", then find the corresponding value in the adjacent cell in column Q,
then sum the values in the cells that meet the criteria and display it in
cell N49.
I don't know if the actual cell references help you or not, but I'm
hoping they will. I'm frustrated no end. I know this can be done, in
theory, because it was done in another application - Word Perfect (more
likely dropped in from Quattro Pro). I just don't know if it can be done in
Excel, or how. The table in Word Perfect is protected and I can't open to
see the formulas and references that were used, and the person who created it
is long gone from our office. I hope someone can help!

select cell value based on value in adjacent cell
 

Put the following formula in cell N49.

=SUMIF(P4:P43,60,Q4:Q43)

Hope this helps.

Bill Horton

"Paige" wrote:

I'm creating a timesheet and running into some trouble calculating total time
usage.
Here's the setup:
I have a column P in which conditional data is entered (e.g., if a person
used Holiday accrual time, they enter code "60"; this field may or may not be
populated, and may use one of 3-4 different codes). The cells in this
column are not contiguous, but broken up by merged cells.
The next coloumn, Q, holds the actual hours of accruals used (general
formatting). The cells in this column are not contiguous, but broken up by
merged cells, matching column P.
Way down at the bottom of the timesheet, I need to calculate the number of
hours of accruals used based on what code is entered in column P. So here's
what I need to figure out how to calculate:
If any cell in column P (P4-43, skipping the merged cells) holds the code
"60", then find the corresponding value in the adjacent cell in column Q,
then sum the values in the cells that meet the criteria and display it in
cell N49.
I don't know if the actual cell references help you or not, but I'm
hoping they will. I'm frustrated no end. I know this can be done, in
theory, because it was done in another application - Word Perfect (more
likely dropped in from Quattro Pro). I just don't know if it can be done in
Excel, or how. The table in Word Perfect is protected and I can't open to
see the formulas and references that were used, and the person who created it
is long gone from our office. I hope someone can help!

select cell value based on value in adjacent cell
 

Good grief - Thanks! I was experimenting with SUMIF, but I'd apparently
constructed it wrong. Whew!

"William Horton" wrote:

Put the following formula in cell N49.

=SUMIF(P4:P43,60,Q4:Q43)

Hope this helps.

Bill Horton

"Paige" wrote:

I'm creating a timesheet and running into some trouble calculating total time
usage.
Here's the setup:
I have a column P in which conditional data is entered (e.g., if a person
used Holiday accrual time, they enter code "60"; this field may or may not be
populated, and may use one of 3-4 different codes). The cells in this
column are not contiguous, but broken up by merged cells.
The next coloumn, Q, holds the actual hours of accruals used (general
formatting). The cells in this column are not contiguous, but broken up by
merged cells, matching column P.
Way down at the bottom of the timesheet, I need to calculate the number of
hours of accruals used based on what code is entered in column P. So here's
what I need to figure out how to calculate:
If any cell in column P (P4-43, skipping the merged cells) holds the code
"60", then find the corresponding value in the adjacent cell in column Q,
then sum the values in the cells that meet the criteria and display it in
cell N49.
I don't know if the actual cell references help you or not, but I'm
hoping they will. I'm frustrated no end. I know this can be done, in
theory, because it was done in another application - Word Perfect (more
likely dropped in from Quattro Pro). I just don't know if it can be done in
Excel, or how. The table in Word Perfect is protected and I can't open to
see the formulas and references that were used, and the person who created it
is long gone from our office. I hope someone can help!