Identify and add formula - can it be done?

[Guest]

I have Excel 2002 and know how to do basic formulas. I'm just not sure if
this can be done

I created a time log with columns labeled:

Time In, Time Out, Total Time (C16:C36), Activity, and Priority (I16:I36).

All 3 time cells are formatted as "1:30 PM" and the Total Time formula is:
=TEXT(B16-A16,"h:mm"). That works just fine.

Numbers will be entered in the Priority column cells, anywhere from 1 to 6.

Can Excel identify a specific number in the Priority column, let's say all
cells containing number 1, and then add the corresponding times from the
Total Time column? So all cells containing the number 1 (I16:I36) will be
matched to their total times in (C16:C36) and those times will be added up?

Thanks!

 

[Franz Verga]

I have Excel 2002 and know how to do basic formulas. I'm just not
sure if this can be done

I created a time log with columns labeled:

Time In, Time Out, Total Time (C16:C36), Activity, and Priority
(I16:I36).

All 3 time cells are formatted as "1:30 PM" and the Total Time
formula is: =TEXT(B16-A16,"h:mm"). That works just fine.

Numbers will be entered in the Priority column cells, anywhere from 1
to 6.

Can Excel identify a specific number in the Priority column, let's
say all cells containing number 1, and then add the corresponding
times from the Total Time column? So all cells containing the number
1 (I16:I36) will be matched to their total times in (C16:C36) and
those times will be added up?

Yes, you can use the SUMIF function. It should be something like this:

=SUMIF(I16:I36,1,C16:C36), but it should be better to modify yor formula in
C16:C36; it should be:

=B16-A16 and copy down till C36.

Then you have to format C16:C36 with custom formatting: "[h]:mm" (without
quotes). You have to use the samecustom format in the cell where you use the
SUMIF.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Rather than using a TEXT function, you could simply subtract the two columns
(=B16-A16) and Format Cells to be "h:mm". Then you could do a SUMIF()
function to sum those cells in B, where the corresponding cell in the
Priority column is equal to, say, 3. Again, you probably will need to format
the function result as "h:mm".

 

[Guest]

Ciao!

Thanks so much for your help. It's working now!

arrivederci

--
KP

I have Excel 2002 and know how to do basic formulas. I'm just not
sure if this can be done

I created a time log with columns labeled:

Time In, Time Out, Total Time (C16:C36), Activity, and Priority
(I16:I36).

All 3 time cells are formatted as "1:30 PM" and the Total Time
formula is: =TEXT(B16-A16,"h:mm"). That works just fine.

Numbers will be entered in the Priority column cells, anywhere from 1
to 6.

Can Excel identify a specific number in the Priority column, let's
say all cells containing number 1, and then add the corresponding
times from the Total Time column? So all cells containing the number
1 (I16:I36) will be matched to their total times in (C16:C36) and
those times will be added up?

Yes, you can use the SUMIF function. It should be something like this:

=SUMIF(I16:I36,1,C16:C36), but it should be better to modify yor formula in
C16:C36; it should be:

=B16-A16 and copy down till C36.

Then you have to format C16:C36 with custom formatting: "[h]:mm" (without
quotes). You have to use the samecustom format in the cell where you use the
SUMIF.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Thanks for your help. It is working now!
--
KP

Rather than using a TEXT function, you could simply subtract the two columns
(=B16-A16) and Format Cells to be "h:mm". Then you could do a SUMIF()
function to sum those cells in B, where the corresponding cell in the
Priority column is equal to, say, 3. Again, you probably will need to format
the function result as "h:mm".