Pulling information from a table base on multiple columns

[mark hansen]

I have a small table that receives data (rows) from multiple people. One of the columns is Hours worked for the month. Other key columns are participant and location of work. Each participant is supposed to submit their information for each place they work Each month. One one person may need to submit 2-3 rows of information each month. All that works great.

I'm having trouble checking to see if they submitted the information. I have a chart with the participant and place where they work in rows, and columns for each month in a year. I can pull the hours worked by using a SUMIFS function and it works well, when they are only numbers in the Hours worked column. This allows the person to put in a zero when they didn't work at that location for the month. I can just pull the value (should only be one row matching the Month/Year, person, and location) to indicate they submitted their report. But if the person is on a business trip for an extended period of time and didn't submit a report, their supervisor is supposed to submit a report for them with text as the hours worked such as "In FL". The SUMIFS won't pick up the text.

Would this be an index match kind of formulas with the match using multiple criteria. Or is there a better way to get the information from the Hours worked column?

 

[Crystalyzer]

Can you post a copy of the worksheet using the XL2BB tool?

 

[mark hansen]

Thanks for the advice.... I was able to come with an INDEX MATCH formula that pulled the value of hours and any text they may have entered.