Pulling information from a table base on multiple columns

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
527
Office Version
  1. 2016
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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Can you post a copy of the worksheet using the XL2BB tool?
 

mark hansen

Well-known Member
Joined
Mar 6, 2006
Messages
527
Office Version
  1. 2016
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.
 

Forum statistics

Threads
1,140,920
Messages
5,703,173
Members
421,279
Latest member
emzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top