SUMIFS with multiple arrays

[Chenboy2]

Copy of Excel Practice.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Trial Balance (in $MMs)
2	Currency: USD
3
4	h	h	Entity Type	Corporation	Partnership	Corporation	Partnership	Corporation	Corporation	Corporation
5	h	h	Jurisdiction	US	US	CA	CA	SZ	NL	NL
6	h	ha	Company	Company A	Company B	Company C	Company D	Company E	Company F	Company G
7	Mapping	GL Account	GL Account	A	B	C	D	E	F	G
8	B/S	Cash	111	100	700	500	500	6,000	1,000	7,000
9	B/S	Loan receivable	112	50	19			5
10	B/S	Investment in CA	113	50
11	B/S	Investment in SZ	114	1,000
12	B/S	Investment in NL	115	6,000					5,000
13	B/S	Investment in US	116	300
14	B/S	A/P	221	(15)
15	B/S	Loan payable	222	(10)				(14)
16	B/S	Long-term debt	223	-	(495)	(490)	(490)	(100)	-	(2,000)
17	B/S	Other liabilities	224	(25)	(20)	(15)	(15)	(10)	(5)	-
18	B/S	Capital Contributions	331	(3,000)	(300)	(50)	(50)	(1,000)	(6,000)	(5,000)
19	B/S	Common Stock	332	-	-	10	10	15	20	25
20	B/S	Distributions	333	200	15	-	-	7	37	10
21	B/S	Retained earnings	334	(4,569)	114	108	108	(4,877)	(106)	(46)
22	PnL	Gross receipts	441	(300)	(345)	(340)	(340)	(335)	(330)	(325)
23	PnL	COGS	551	175	150	185	185	190	195	200
24	PnL	Depreciation	552	27	50	37	37	42	47	100
25	PnL	Interest T-Bills	553	(5)	-	-	-	-	-	-
26	PnL	Bank interest	554	(6)	105	20	20	50	120	35
27	PnL	Other interest	555	3	7	12	12	17	22	1
28	PnL	US Tax Expense	661	22	-	-	-	-	-	-
29	PnL	State Tax Expense	662	3	-	-	-	-	-	-
30	PnL	Foreign Tax Expense	663	-	-	23	23	10	-	-
31			TAX	28	7	35	35	27	22	1
Master

Cell Formulas
Range	Formula
D7:J7	D7	=RIGHT(D6,1)
D31	D31	=SUM(D27:D29)
E31:J31	E31	=SUM(E27:E30)

Copy of Excel Practice.xlsx
	A	B	C	D	E
1	Total Tax Provision by enity type and jurisdiction
2
3
4
5		US	CA	SZ	NL
6	Company A	34.7722	70.44	26.72	23
7	Company B	34.7722	70.44	26.72	23
8	Company C	27.7722
9	Company D	27.7722
10	Company E	27.7722
11	Company F	27.7722
12	Company G
Problem 7

Cell Formulas
Range	Formula
B7:E7	C7	=SUMIFS(Master!$D31:$J31,Master!$D5:$J5,{"US","CA","SZ","NL"})
B6:E6	B6	=SUMIFS(Master!$D$31:$J$31,Master!$D$5:$J$5,{"US","CA","SZ","NL"})
B8:B11	B8	=SUMIFS(Master!$D$31:$J$31,Master!$D$5:$J$5,{"US","CA","SZ","NL"},Master!$D$6:$J$6,{"Company A","Company B","Company C","Company D","Company E","Company F","Company G"})
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Hi,

This student I'm tutoring is trying to complete #7, Total tax provision by entity type, and jurisdiction.

I got this for Company A: =SUMIFS(Master!$D$31:$J$31,Master!$D$5:$J$5,{"US","CA","SZ","NL"}), and it works

It doesn't work for Company B-G.

Thanks!

 

[StephenCrump]

I got this for Company A: =SUMIFS(Master!$D$31:$J$31,Master!$D$5:$J$5,{"US","CA","SZ","NL"}), and it work

This formula is returning values for companies A through G, not just Company A, so I'm not clear how you can regard this as "working"?

What results do you expect to see for Companies B, C .... G?

(I'm also not clear why you've hard-coded {"US","CA","SZ","NL"} rather than simply pointing to your row headers, currently - but not necessarily - containing the same values).

 

[Chenboy2]

I turns out that it didn't work. Sorry, I thought that it worked.

The student set up the answer wrong.

It's supposed to be entity and jurisdiction, so it would be actually "Corporation" and "Partnership", and {US","CA","SZ","NL", and not Companies A-G.

 

[StephenCrump]

In 365, you can do it like this:

	A	B	C	D	E	F	G	H	I	J
1
2
3
4	h	h	Entity Type	Corporation	Partnership	Corporation	Partnership	Corporation	Corporation	Corporation
5	h	h	Jurisdiction	US	US	CA	CA	SZ	NL	NL
6	h	ha	Company	Company A	Company B	Company C	Company D	Company E	Company F	Company G
7	Mapping	GL Account	GL Account	A	B	C	D	E	F	G
8
9
10
11	PnL	Other interest	555	3	7	12	12	17	22	1
12	PnL	US Tax Expense	661	21.5922	0	0	0	0	0	0
13	PnL	State Tax Expense	662	3.18	0	0	0	0	0	0
14	PnL	Foreign Tax Expense	663	0	0	23.22	23.22	9.72	0	0
15			TAX	27.7722	7	35.22	35.22	26.72	22	1
16
17
18
19
20				Tax summary	US	CA	SZ	NL
21				Corporation	27.7722	35.22	26.72	23
22				Partnership	7	35.22	0	0
Master

Cell Formulas
Range	Formula
D7:J7	D7	=RIGHT(D6,1)
D15	D15	=SUM(D11:D13)
E15:J15	E15	=SUM(E11:E14)
E21:H22	E21	=SUMIFS(D15:J15,D5:J5,E20:H20,D4:J4,D21:D22)
Dynamic array formulas.

By the way, your SUM() formula for TAX don't look correct - they include an interest item, and column D doesn't include the foreign tax expense.