Searching for a number in a list

[Paul Peterson - Velox Consulting, LLC]

Hi - I'm trying to do the following:

 

[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

 

[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))

 

[Paul Peterson - Velox Consulting, LLC]

thanks for the quick reply - I'll try this too.

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!*

 

[Paul Peterson - Velox Consulting, LLC]

thanks for the quick reply - I'll try this.

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

 

[Paul Peterson - Velox Consulting, LLC]

Pete, I just realized I don't know how to back up one row - can you help?