SumIf with dynamic column from Drop Down

[ekdrake]

Hello. I am trying to create a SumIf that changes columns depending on the dropdown value selected. I have tried Indirect, Match, and Offset, but I just can't figure it out. I'm frustrated because I've been trying for days and I know this should be simple!!

Basically, B1 is my drop down, which has subgroup options that match F2 to O2. My SumIf in C1 says: =SUMIFS(F3:F324,A3:A324,"=1"). How do I revise my basic formula to get F3:F324 to change columns dynamically based on what's selected in B1?

Thanks!

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Select Subgroup =>	Total	149,024,359
2	Sumif Indicator		LookupID:	Year	Age Group	Total	Male Total	Female Total	White Total	White Male	White Female	Black Total	Black Male	Black Female	AIAN Total
3			20000-4	2000	0-4	394,303	202,064	192,240	319,901	164,339	155,562	16,806	8,558	8,249	8,093
4	1		20005-9	2000	5-9	425,908	218,500	207,409	351,166	180,308	170,858	18,117	9,347	8,770	9,129
5	1		200010-14	2000	10-14	434,835	222,937	211,899	362,011	185,615	176,396	17,938	9,362	8,576	9,747
6	1		200015-19	2000	15-19	427,965	220,407	207,557	356,013	183,846	172,167	16,677	8,919	7,758	9,067
7	1		200020-24	2000	20-24	390,193	200,816	189,377	324,005	167,146	156,859	16,246	8,977	7,269	7,584
8	1		200025-29	2000	25-29	403,652	206,789	196,863	336,267	172,774	163,493	16,305	9,031	7,274	7,407
9			200030-34	2000	30-34	437,480	223,413	214,067	371,256	190,431	180,826	17,583	9,802	7,781	7,474
10			200035-39	2000	35-39	483,948	244,034	239,914	416,878	211,071	205,807	18,568	10,291	8,277	8,032
11			200040-44	2000	40-44	491,145	246,268	244,877	428,300	215,729	212,572	17,098	9,658	7,441	7,767
12			200045-49	2000	45-49	454,231	225,512	228,719	401,403	200,204	201,199	13,116	7,354	5,762	6,552
13			200050-54	2000	50-54	391,757	195,063	196,694	350,576	175,406	175,171	9,632	5,218	4,415	5,211

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Test​

 

[HackSlash]

Try this:

=SUM(INDEX(D3:P13,0,MATCH(B1,D2:P2,0)))

This will sum the column, which is constrained in the first range D3:P13, change as needed. The match clearly is looking at your dropdown and comparing with the 2nd row.

 

[steve the fish]

Or this if you want to use the 1 in column A

=SUMIFS(INDEX(D3:O13,,MATCH(B1,D2:O2,0)),A3:A13,1)

 

[ekdrake]

Or this if you want to use the 1 in column A

=SUMIFS(INDEX(D3:O13,,MATCH(B1,D2:O2,0)),A3:A13,1)

This works!!! Thank you so much! I don't know whether to laugh or cry given how much time I've spent on this!

I think I was going wrong on the INDEX, where you skip the row argument with the ",,". I also learned that I can just put the 1 for the sumif instead of "=1". I didn't know that.

At any rate, this works! Thank you.

 

[Peter_SSs]

.. and another option would be
=SUMPRODUCT(F3:O13,(F2:O2=B1)*(A3:A13=1))