CODEWITHSUNDEEP
excelsumproduct
Benefit Guy
Benefit Guy

Reputation: 11

Excel Sumproduct for filtered data

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.

enter image description here

Upvotes: 1

Views: 1373

Answers (1)

K753
K753

Reputation: 388

Subtotal and Offset will not help you in this situation.

The easiest solution is to determine what it being filtered on the Benefit tab and add that criteria to your formula. It looks like the CC# column is filtered to equal "Office". If that's the case then your formula would look like this:

SUMPRODUCT formula:=IFERROR(SUMPRODUCT((Benefit!C:C=A1)+(Benefit!C:C=A2)+(Benefit!A:A="Office"),Benefit!H:H),0)

Upvotes: 0

Related Questions