Can someone help me develop an excel formula for my project. The premise of the situation is based on a document that I receive a weekly document from corporate which breaks down benefit expenses per employee. My job is to set up an excel ‘template” that uses the weekly document as a source file to pull information and calculate the sum of each employee’s individual expense. Initially the solution was a simple IF and vlookup formula that 1) looked if an employee was on the list ->iferror backup measure in case the employee wasn’t listed for a given week. 2) Recorded their expense. The initial formula I used was:
=IFERROR(VLOOKUP(A1,Benefit!C:H,6,FALSE),0)
For example: 1) A1 = the employee in question. 2) Column H is the expense I need to charge to that employee. 3) Benefits tab is the source file I am pulling information. When I received the following week’s document, I realized this time that some of the employee’s names included their middle initial and didn’t match exactly to my reference list. Some employees also had two charges, so now I needed to lookup multiple references and sum the collective expenses for that individual. Ok-> SUMPRODUCT will work perfect to sum Arrays for multiple references. I expanded my employee reference list to include both spellings and replaced the single source vlookup for a
SUMPRODUCT formula: =IFERROR(SUMPRODUCT((Benefit!C:C=A1)+(Benefit!C:C=A2),Benefit!H:H),0)
Now the part I am struggling with is that the benefits tab filters out some of the rows but the sumproduct formula still pulls data from those rows. So, the result for employee1 was $35.30. I’ve scoured through forums and toyed with incorporating “subtotal” and “offset” which seems to be everyone’s solution for excluding filtered data, but I can’t make any sense of it.
