P
Paul Peterson - Velox Consulting, LLC
Hi - I'm trying to do the following:
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
Pete_UK
Do you have a blank row after the total and then a new name? If so,
can you find the next name after Joe Smith (are they sorted?) and then
come back two rows?
MATCH will return the (relative) row in a range that a matched item is
found on, or you can look through the whole column and thus return the
absolute row.
Hope this helps.
Pete
can you find the next name after Joe Smith (are they sorted?) and then
come back two rows?
MATCH will return the (relative) row in a range that a matched item is
found on, or you can look through the whole column and thus return the
absolute row.
Hope this helps.
Pete
L
Luke M
Is there some text or something that indicates its the total row? For
instance, if column A is the name, Column B is what he did or "Total", and
Column C is the hours, you could do
=SUMPRODUCT((A2:A100="Smith, Joe")*(B2:B100="Total")*(C2:C100))
If its just that the total row is the last row under that name (hours is in
column B)
=SUMPRODUCT((A2:A100="Smith, Joe")*(A2:A100<>A3:A101)*(B2:B100))
instance, if column A is the name, Column B is what he did or "Total", and
Column C is the hours, you could do
=SUMPRODUCT((A2:A100="Smith, Joe")*(B2:B100="Total")*(C2:C100))
If its just that the total row is the last row under that name (hours is in
column B)
=SUMPRODUCT((A2:A100="Smith, Joe")*(A2:A100<>A3:A101)*(B2:B100))
P
Paul Peterson - Velox Consulting, LLC
thanks for the quick reply - I'll try this too.
Luke M said:Is there some text or something that indicates its the total row? For
instance, if column A is the name, Column B is what he did or "Total", and
Column C is the hours, you could do
=SUMPRODUCT((A2:A100="Smith, Joe")*(B2:B100="Total")*(C2:C100))
If its just that the total row is the last row under that name (hours is in
column B)
=SUMPRODUCT((A2:A100="Smith, Joe")*(A2:A100<>A3:A101)*(B2:B100))
--
Best Regards,
Luke M
*Remember to click "yes" if this post helped you!*
P
Paul Peterson - Velox Consulting, LLC
thanks for the quick reply - I'll try this.
Pete_UK said:Do you have a blank row after the total and then a new name? If so,
can you find the next name after Joe Smith (are they sorted?) and then
come back two rows?
MATCH will return the (relative) row in a range that a matched item is
found on, or you can look through the whole column and thus return the
absolute row.
Hope this helps.
Pete
P
Paul Peterson - Velox Consulting, LLC
Pete, I just realized I don't know how to back up one row - can you help?