SUMIFS Across Two Tables (or some other solution)

[mjweber1]

Hello Everyone,

I'm racking my brain in trying to figure out a formula that will solve the following scenario.

I have a main table on a "Costs" sheet that contains a number of people that have monthly costs like so:

I then have another table on another sheet, call it "Cost Centers" that tracks the cost center for each person like so:

I now need to summarize the expenses in the below table on a monthly basis for each vendor (per Cost Center) that I have in the "Costs" table:

I also have an identical table for "Cost Center" BB.

My initial thought was to try to use a SUMIFS but that hasn't worked thus far. Does anyone have any suggestions?

Thanks in advance!

 

[dreid1011]

Maybe SUMPRODUCT. Does this look like what you expect?

Also, what version of Excel are you using? You should update your profile to show which version to get the best possible answers.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Costs
2	Name	Vendor	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
3	Bob	Star	100	100	110	90	110	130	100	100	120	130	140	80
4	Sue	Square	100	100	120	130	140	80	100	100	120	130	140	80
5	Tim	Star	50	46	60	75	80	40	50	80	80	90	30	100
6
7	Cost Center
8	Name	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
9	Bob	AA	AA	AA	AA	AA	AA	AA	AA	AA	AA	AA	AA
10	Sue	AA	AA	AA	AA	AA	AA	AA	AA	AA	AA	AA	AA
11	Tim	BB	BB	BB	BB	BB	BB	AA	AA	AA	AA	AA	AA
12
13	Cost Center Vendor Summary
14	AA	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
15	Star	100	100	110	90	110	130	150	180	200	220	170	180	1740
16	Square	100	100	120	130	140	80	100	100	120	130	140	80	1340
17
18	Cost Center Vendor Summary
19	BB	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Total
20	Star	50	46	60	75	80	40	0	0	0	0	0	0	351
21	Square	0	0	0	0	0	0	0	0	0	0	0	0	0
Sheet3

Cell Formulas
Range	Formula
B15:M16	B15	=SUMPRODUCT($C$3:$N$5*($B$3:$B$5=$A15)*($B$9:$M$11=$A$14)*($C$2:$N$2=B$14))
N15:N16,N20:N21	N15	=SUM(B15:M15)
B20:M21	B20	=SUMPRODUCT($C$3:$N$5*($B$3:$B$5=$A20)*($B$9:$M$11=$A$19)*($C$2:$N$2=B$19))