HLOOKUP multiple values with SUMPRODUCT?

[megera716]

I have a huge export out of data out of our accounting system that lists all our departments down one side and all P&L account names across the top. I update my reporting monthly and sometimes the P&L account name shifts one or multiple columns as expenses in more accounts are introduced throughout the year. (E.g., at the beginning of the year, we might just have payroll, but by the end we have payroll, health insurance, travel, contractors, etc. etc. etc.). I copied and pasted a bunch of random amounts from other sources so you'd get the idea :P.

What I want in the Total Employee Expenses column at the end is the sum of all the values on that row from the Health Insurance, Employee Benefits or Payroll Expenses columns (the accounts in the first row). I can't just do a straight SUM across because I update this monthly and the order of the P&L accounts shifts around on the export from the accounting system and as you can see there is Rent and Travel interspersed. Hopefully this makes sense!!


I found this =SUM(VLOOKUP(A10, $A$2:$F$7, {2,3,4,5,6}, FALSE)) in my internet search but that would work if my values were in multiple columns, but mine are in multiple rows and I think I need the SUMPRODUCT because I don't always want to return just the first value it finds. I don't know if I'm making any sense ?

Health Insurance

Rent

Employee Benefits

Payroll Expenses

Health Insurance

Employee Benefits

Payroll Expenses

Travel

Employee Benefits

Health Insurance

Travel

Payroll Expenses

(Administrative)

(Administrative)

(Administrative)

(Administrative)

(Sales)

(Sales)

(Sales)

(Sales)

(Product)

(Product)

(Product)

(Product)

Total Employee Expenses

Department1

84,259​

36,032​

104,903​

94,705​

74,294​

83,784​

0​

85,628​

Department2

23,368​

84,622​

80,052​

81,968​

155​

13,683​

Department3

18,507​

103,995​

105,373​

116,313​

5,587​

10,636​

Department4

84,259​

13,192​

104,903​

94,705​

25,814​

74,294​

83,784​

0​

85,628​

Department5

47,142​

84,622​

80,052​

81,968​

10,374​

0​

Department6

11,229​

103,995​

105,373​

116,313​

0​

1,548​

Department7

84,259​

7,534​

104,903​

94,705​

21,365​

74,294​

83,784​

0​

85,628​

Department8

5,089​

84,622​

80,052​

81,968​

0​

0​

Department9

6,460​

103,995​

105,373​

116,313​

0​

7,232​

Department10

84,259​

5,614​

104,903​

94,705​

0​

74,294​

83,784​

0​

85,628​

0​

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Health Insurance	Rent	Employee Benefits	Payroll Expenses	Health Insurance	Employee Benefits	Payroll Expenses	Travel	Employee Benefits	Health Insurance	Travel	Payroll Expenses
2		(Administrative)	(Administrative)	(Administrative)	(Administrative)	(Sales)	(Sales)	(Sales)	(Sales)	(Product)	(Product)	(Product)	(Product)	Total Employee Expenses
3	Department1	84259	30032	104903	94705					74294	83784	0	85628	527573
4	Department2	23368			84622	80052	81968	155			13683			283848
Sheet1

Cell Formulas
Range	Formula
N3:N4	N3	=SUM(FILTER(B3:M3,($B$1:$M$1="Health Insurance")+($B$1:$M$1="Employee Benefits")+($B$1:$M$1="Payroll Expenses")))

 

[megera716]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Health Insurance	Rent	Employee Benefits	Payroll Expenses	Health Insurance	Employee Benefits	Payroll Expenses	Travel	Employee Benefits	Health Insurance	Travel	Payroll Expenses
2		(Administrative)	(Administrative)	(Administrative)	(Administrative)	(Sales)	(Sales)	(Sales)	(Sales)	(Product)	(Product)	(Product)	(Product)	Total Employee Expenses
3	Department1	84259	30032	104903	94705					74294	83784	0	85628	527573
4	Department2	23368			84622	80052	81968	155			13683			283848
Sheet1

Cell Formulas
Range	Formula
N3:N4	N3	=SUM(FILTER(B3:M3,($B$1:$M$1="Health Insurance")+($B$1:$M$1="Employee Benefits")+($B$1:$M$1="Payroll Expenses")))

Thank you! I am not even familiar with FILTER as a function! I’ll give this a try in the morning

 

[AhoyNC]

FILTER is a new function in Excel 365.

 

[Fluff]

Another option, if you haven't got the Filter function yet.

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Health Insurance	Rent	Employee Benefits	Payroll Expenses	Health Insurance	Employee Benefits	Payroll Expenses	Travel	Employee Benefits	Health Insurance	Travel	Payroll Expenses
2		(Administrative)	(Administrative)	(Administrative)	(Administrative)	(Sales)	(Sales)	(Sales)	(Sales)	(Product)	(Product)	(Product)	(Product)	Total Employee Expenses
3	Department1	84,259	36,032	104,903	94,705					74,294	83,784	0	85,628	527573
4	Department2	23,368			84,622	80,052	81,968	155			13,683			283848
5	Department3	18,507			103,995	105,373	116,313	5,587			10,636			360411
6	Department4	84,259	13,192	104,903	94,705				25,814	74,294	83,784	0	85,628	527573
7	Department5	47,142			84,622	80,052	81,968	10,374			0			304158
8	Department6	11,229			103,995	105,373	116,313	0			1,548			338458
9	Department7	84,259	7,534	104,903	94,705				21,365	74,294	83,784	0	85,628	527573
10	Department8	5,089			84,622	80,052	81,968	0			0			251731
11	Department9	6,460			103,995	105,373	116,313	0			7,232			339373
12	Department10	84,259	5,614	104,903	94,705				0	74,294	83,784	0	85,628	527573
Main

Cell Formulas
Range	Formula
N3:N12	N3	=SUM(SUMIFS(B3:M3,$B$1:$M$1,{"Health Insurance","Employee Benefits","Payroll Expenses"}))

 

[megera716]

FILTER is a new function in Excel 365.

I ended up not getting to this yesterday but I tried it just now and it works beautifully!

@Fluff, I also tried yours which worked fine as well as you wrote it but it did not like me when I tried to add a cell reference as another criteria. I also tried using Ctrl-Shift-Enter, noting the curly brackets.

However, thank you both for your help! I love this forum!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[AhoyNC]

You're welcome. Thanks for the feedback.