Easiest way to get total.

[niles08]

I am trying to piece together this Excel spreadsheet to make it easier for our customers to get their totals based on zip code. The biggest issue is that our customers are used to picking their zoning by counties(which we can no longer provide). Switching to zip codes has multiple zip codes in several counties(because of the way the United States is laid out). What I am attempting to create is a basic "check this box" if you want this county and it will total up the zip codes in that county for you and add it to the grand total.

For example, if you want Anoka County, you check the box in the image, and you get 41, because 55038 and 55330 have residents in another county as well that have to be added since it is by zip code. What I need to have happen is when you click on Anoka, this is automated, and you get the 41 automatically added to the total in cell D1. I have un hid all the cells, but my plan is to hide the cells with "zip codes" so that customers are only seeing the cells with the checkboxes. I am having to currently manually go in and change each checkbox to assign it to work individually with the cell next to it, and also change the formulas to match the zip codes below the county(and find the duplicates as well). Is there an easier way, since this is a list of over 1,200 zip codes. I do have the "duplicates" formatted in red so that they are easy to see.

In a perfect world:

When Anoka County was selected, it would add up all of the zip codes below it, AND automatically grab the other 2 zip codes that are duplicated in another county. So 55038 for instance is also found in cell 1182(and has 8 people), so the 8 would be added to the Anoka County total. It is currently doing this, but it was a TON of manual work, before going any further than I have gone I was hoping somebody would have a better suggestion.

Below is a link to the full spreadsheet and the mini sheet which couldn't do all of the sheet but gives a taste. I would greatly appreciate ANY help.

Loading Google Sheets

Total By Zip.xlsx
	A	B	C	D
1			Total	41
2
3	Row Labels	Count of ZIP
4	ANOKA COUNTY		TRUE	41
5	55005	1
6	55011	1
7	55014	1
8	55038	4
9	55303	3
10	55304	1
11	55330	6
12	55432	1
13	55433	1
14	55448	1
15	55449	2
16	BARNES COUNTY		FALSE	0
17	58481	1
18	BARRON COUNTY		FALSE	0
19	54812	1
20	BAYFIELD COUNTY		FALSE	0
21	54891	1
22	BECKER COUNTY		FALSE	0
23	56501	2
24	56521	2
25	56544	1
26	56554	5
27	56569	2
28	56578	1
29	56585	1
30	56589	2
31	BELTRAMI COUNTY		FALSE	0
32	56601	1
33	BENTON COUNTY		FALSE	0
42	BIG STONE COUNTY	55
43	56208	1
44	56210	2
45	56211	6
46	56221	1
47	56225	8
48	56227	8
49	56240	14
50	56276	3
51	56278	12
52	BLUE EARTH COUNTY	374
53	56001	103
54	56002	2
55	56010	41
56	56024	9
57	56034	17
58	56037	39
59	56048	12
60	56055	46
61	56060	2
62	56062	9
63	56063	7
64	56065	50
65	56068	12
66	56073	3
67	56078	2
68	56080	3
69	56088	1
70	56090	16
71	BOONE COUNTY	2
72	50036	1
73	50134	1
74	BOYLE COUNTY	1
75	40422	1
76	BREMER COUNTY	1
77	50668	1
78	BREVARD COUNTY	1
79	32901	1
80	BROOKINGS COUNTY	3
81	57026	2
82	57276	1
83	BROWN COUNTY	351
84	56019	25
85	56041	28
86	56062	3
87	56073	106
88	56081	3
89	56083	5
90	56085	108
91	56087	62
92	56266	11
93	BUENA VISTA COUNTY	1
94	50568	1
95	BUFFALO COUNTY	1
96	54755	1
97	BURNETT COUNTY	1
98	54893	1
99	BUTLER COUNTY	3
100	50602	1
101	50604	1
102	50665	1
103	CALHOUN COUNTY	1
104	50563	1
105	CARLTON COUNTY	5
106	55707	1
107	55749	1
108	55757	1
109	55767	2
110	CARVER COUNTY	199
111	55315	3
112	55317	4
113	55318	16
114	55322	42
115	55328	2
116	55339	8
117	55360	15
118	55363	2
119	55367	8
120	55368	33
121	55386	3
122	55387	22
123	55388	16
124	55395	2
125	55397	18
126	56011	5
127	CASS COUNTY	15
128	56435	1
129	56466	4
130	56468	1
131	56473	1
132	56474	2
133	56626	1
134	58078	1
135	58104	4
136	CEDAR COUNTY	1
137	52255	1
138	CERRO GORDO COUNTY	1
139	50428	1
140	CHEROKEE COUNTY	3
141	51012	2
142	51025	1
143	CHESAPEAKE CITY	1
144	23320	1
145	CHIPPEWA COUNTY	126
146	54724	1
147	54729	2
148	56208	1
149	56222	26
150	56241	5
151	56252	4
152	56260	19
153	56262	6
154	56265	43
155	56271	7
156	56282	9
157	56295	3
158	CHISAGO COUNTY	9
159	55008	1
160	55012	1
161	55013	1
162	55032	1
163	55045	1
164	55056	1
165	55069	2
166	55092	1
167	CLARK COUNTY	1
168	54436	1
169	CLAY COUNTY	60
170	51301	3
171	51333	1
172	51343	1
173	56514	3
174	56536	7
175	56546	2
176	56547	3
177	56549	9
178	56552	3
179	56560	17
180	56580	5
181	56585	6
182	CLAYTON COUNTY	2
183	52043	1
184	52157	1
185	CLEARWATER COUNTY	2
186	56644	1
187	56676	1
188	CODINGTON COUNTY	1
189	57243	1
190	COOK COUNTY	1
191	60439	1
192	COTTONWOOD COUNTY	189
193	56019	14
194	56083	14
195	56101	42
196	56118	12
197	56131	4
198	56137	7
199	56145	19
200	56152	13
201	56159	37
202	56166	2
203	56174	6
204	56180	2
205	56183	17
206	CROW WING COUNTY	19
207	56364	6
208	56401	4
209	56425	2
210	56431	1
211	56447	1
212	56448	3
213	56465	1
214	56474	1
215	DAKOTA COUNTY	164
216	55009	10
217	55024	32
218	55031	13
219	55033	42
220	55044	10
221	55057	16
222	55065	9
223	55068	11
224	55076	1
225	55077	2
226	55085	1
227	55118	2
228	55120	2
229	55121	1
230	55122	1
231	55123	2
232	55124	7
233	55337	2
234	DALLAS COUNTY	4
235	50266	1
236	50323	3
237	DENTON COUNTY	1
238	76210	1
239	DEUEL COUNTY	7
240	57213	1
241	57218	3
242	57237	3
243	DICKINSON COUNTY	13
244	51331	1
245	51338	1
246	51347	5
247	51351	3
248	51360	2
249	51364	1
250	DODGE COUNTY	112
251	53963	1
252	55917	8
253	55920	1
254	55924	14
255	55927	23
256	55940	15
257	55944	23
258	55946	2
259	55955	9
260	55963	1
261	55985	15
262	DOUGLAS COUNTY	100
263	54854	1
264	56308	23
265	56315	12
266	56319	5
267	56326	14
268	56327	4
269	56332	4
270	56339	1
271	56341	1
272	56343	5
273	56354	6
274	56355	5
275	56360	14
276	56361	4
277	56446	1
278	DUNN COUNTY	2
279	54751	2
280	EAU CLAIRE COUNTY	4
281	54701	1
282	54722	1
283	54742	1
284	54770	1
285	EMMET COUNTY	11
286	50514	6
287	50531	2
288	51334	2
289	51365	1
290	ERIE COUNTY	1
291	14222	1
292	FARIBAULT COUNTY	255
293	56013	54
294	56014	24
295	56023	16
296	56025	25
297	56027	11
298	56033	8
299	56039	1
300	56051	6
301	56068	12
302	56097	66
303	56098	32
304	FAYETTE COUNTY	3
305	50629	2
306	52141	1
307	FILLMORE COUNTY	166
308	55922	2
309	55923	12
310	55935	13
311	55939	14
312	55949	19
313	55951	1
314	55954	11
315	55961	5
316	55962	11
317	55965	24
318	55971	6
319	55975	25
320	55976	4
321	55979	2
322	55990	17
323	FINNEY COUNTY	1
324	67846	1
325	FLOYD COUNTY	2
326	50468	2
327	FRANKLIN COUNTY	4
328	50420	1
329	50441	1
330	50452	1
331	50475	1
332	FREEBORN COUNTY	237
333	55912	12
334	55917	7
335	55953	1
336	56007	76
337	56009	37
338	56016	8
339	56026	13
340	56029	10
341	56032	1
342	56035	1
343	56036	32
344	56042	12
345	56043	7
346	56045	6
347	56072	6
348	56097	8
349	GOODHUE COUNTY	233
350	55009	27
351	55018	13
352	55027	42
353	55033	1
354	55041	11
355	55053	7
356	55065	1
357	55066	21
358	55089	18
359	55946	30
360	55956	1
361	55963	12
362	55983	12
363	55985	3
364	55992	34
365	GRAND FORKS COUNTY	1
366	58201	1
367	GRANT COUNTY	50
368	56248	9
369	56274	8
370	56309	1
371	56311	1
372	56326	1
373	56339	10
374	56531	12
375	56590	5
376	57216	2
377	57252	1
378	HAMILTON COUNTY	1
379	50248	1
380	HAMLIN COUNTY	1
381	57223	1
382	HANCOCK COUNTY	5
383	50423	1
384	50430	1
385	50436	1
386	50483	1
387	50484	1
388	HAND COUNTY	1
389	57362	1
390	HANSON COUNTY	1
391	57340	1
392	HENNEPIN COUNTY	87
393	55305	2
394	55311	2
395	55316	2
396	55327	2
397	55331	3
398	55340	12
399	55346	1
400	55347	1
401	55356	2
402	55357	5
403	55359	9
404	55364	2
405	55369	2
406	55374	6
407	55388	1
408	55391	1
409	55403	1
410	55405	2
411	55406	3
412	55408	1
413	55409	1
414	55410	1
415	55412	1
416	55413	1
417	55415	1
418	55416	1
419	55417	1
420	55418	1
421	55422	1
422	55428	4
423	55431	3
424	55435	2
425	55437	1
426	55438	1
427	55439	1
428	55443	3
429	55446	1
430	55447	2
431	HOUSTON COUNTY	56
432	36303	1
433	55921	19
434	55931	2
435	55941	3
436	55943	12
437	55947	1
438	55954	1
439	55971	3
440	55974	14
441	HOWARD COUNTY	7
442	50466	2
443	50628	2
444	52134	1
445	52136	2
446	HUBBARD COUNTY	1
447	56470	1
448	HUMBOLDT COUNTY	2
449	50520	1
450	50577	1
451	ISANTI COUNTY	38
452	55006	6
453	55008	9
454	55017	3
455	55040	8
456	55056	1
457	55070	1
458	55080	4
459	55371	6
460	ITASCA COUNTY	4
461	55709	1
462	56628	2
463	56636	1
464	JACKSON COUNTY	185
465	56101	13
466	56111	6
467	56119	12
468	56127	1
469	56137	18
470	56143	59
471	56150	48
472	56159	2
473	56160	2
474	56161	12
475	56167	5
476	56187	6
477	97524	1
478	KANABEC COUNTY	16
479	55006	3
480	55051	6
481	56358	7
482	KANDIYOHI COUNTY	242
483	56201	68
484	56209	32
485	56216	8
486	56251	8
487	56252	2
488	56253	18
489	56273	26
490	56279	10
491	56281	15
492	56282	17
493	56284	7
494	56288	15
495	56289	8
496	56312	4
497	56362	4
498	KANE COUNTY	2
499	60124	1
500	60175	1
501	KENOSHA COUNTY	1
502	53158	1
503	KENT COUNTY	1
504	49331	1
505	KITTSON COUNTY	30
506	56728	8
507	56731	4
508	56732	5
509	56733	10
510	56735	2
511	56755	1
512	KOSSUTH COUNTY	22
513	50424	4
514	50430	1
515	50451	2
516	50511	6
517	50517	3
518	50556	4
519	50590	1
520	50598	1
521	LA CROSSE COUNTY	1
522	54636	1
523	LABETTE COUNTY	1
524	67357	1
525	LAC QUI PARLE COUNTY	132
526	56212	16
527	56218	10
528	56220	2
529	56223	1
530	56232	35
531	56256	48
532	56257	9
533	56265	9
534	56276	1
535	56278	1
536	LE SUEUR COUNTY	256
537	56011	5
538	56017	15
539	56028	12
540	56044	4
541	56050	13
542	56052	13
543	56057	54
544	56058	33
545	56063	6
546	56069	39
547	56071	29
548	56082	8
549	56096	25
550	LEE COUNTY	1
551	33903	1
552	LINCOLN COUNTY	106
553	56113	5
554	56136	19
555	56142	25
556	56149	20
557	56164	1
558	56170	2
559	56178	27
560	56220	1
561	56280	3
562	56291	2
563	83128	1
564	LINN COUNTY	1
565	52302	1
566	LYON COUNTY	239
567	51237	1
568	51239	1
569	51241	1
570	51243	4
571	51246	3
572	56113	1
573	56115	22
574	56132	9
575	56157	11
576	56169	10
577	56170	1
578	56175	20
579	56178	7
580	56220	1
581	56229	27
582	56239	10
583	56258	83
584	56263	2
585	56264	21
586	56291	4
587	MAHNOMEN COUNTY	4
588	56557	2
589	56589	2
590	MARICOPA COUNTY	2
591	85044	1
592	85351	1
593	MARION COUNTY	1
594	46268	1
595	MARSHALL COUNTY	40
596	56701	4
597	56710	1
598	56713	1
599	56724	2
600	56725	3
601	56727	4
602	56737	2
603	56738	3
604	56757	11
605	56758	1
606	56760	4
607	56762	4
608	MARTIN COUNTY	246
609	56013	11
610	56027	1
611	56031	64
612	56039	33
613	56088	26
614	56098	3
615	56111	1
616	56121	7
617	56127	13
618	56160	3
619	56162	6
620	56171	37
621	56176	23
622	56181	18
623	MCLEOD COUNTY	278
624	55312	26
625	55336	65
626	55338	1
627	55339	2
628	55350	83
629	55354	22
630	55370	8
631	55381	35
632	55385	19
633	55395	15
634	55396	1
635	55397	1
636	MEEKER COUNTY	211
637	55324	9
638	55325	32
639	55329	15
640	55350	6
641	55353	11
642	55355	60
643	55382	3
644	55389	26
645	56209	5
646	56228	15
647	56243	22
648	56362	7
649	MILLE LACS COUNTY	39
650	55371	12
651	56330	5
652	56353	18
653	56357	3
654	56386	1
655	MINNEHAHA COUNTY	11
656	57003	1
657	57005	3
658	57022	1
659	57068	1
660	57104	1
661	57106	1
662	57110	3
663	MITCHELL COUNTY	10
664	50455	3
665	50461	2
666	50472	4
667	50476	1
668	MONROE COUNTY	1
669	54656	1
670	MONTGOMERY COUNTY	3
671	20854	1
672	51573	1
673	77386	1
674	MOODY COUNTY	1
675	57026	1
676	MORRISON COUNTY	188
677	56314	13
678	56318	3
679	56328	1
680	56329	6
681	56338	13
682	56340	3
683	56345	56
684	56364	40
685	56373	26
686	56382	14
687	56384	1
688	56443	1
689	56449	2
690	56475	9
691	MORTON COUNTY	1
692	58554	1
693	MOWER COUNTY	179
694	55909	24
695	55912	44
696	55917	6
697	55918	5
698	55926	8
699	55933	3
700	55936	21
701	55940	1
702	55951	8
703	55953	5
704	55961	3
705	55967	9
706	55970	11
707	55973	3
708	55977	12
709	55982	16
710	MURRAY COUNTY	164
711	56114	4
712	56115	12
713	56122	18
714	56123	7
715	56128	2
716	56131	31
717	56132	8
718	56141	5
719	56151	25
720	56170	1
721	56172	28
722	56175	7
723	56180	4
724	56183	8
725	56186	4
726	NATRONA COUNTY	1
727	82601	1
728	NICOLLET COUNTY	256
729	55332	9
730	55334	1
731	55335	10
732	56003	41
733	56021	27
734	56054	27
735	56058	1
736	56073	43
737	56074	43
738	56082	54
739	NOBLES COUNTY	189
740	56110	29
741	56117	5
742	56119	15
743	56122	3
744	56128	5
745	56129	8
746	56131	13
747	56141	2
748	56147	1
749	56153	3
Sheet5

Cell Formulas
Range	Formula
D1	D1	=SUM(D3:D500)
D4	D4	=IF(C4,SUM(B5:B15)+B112+B1016+B1256,0)
D16,D31,D20,D18	D16	=IF(C16,B17,0)
D22	D22	=IF(C22,SUM(B23:B30)+B810+B181+B589,0)
D33	D33	=IF(C33,SUM(B34:B41)+B1019+B6801+B1020+B651+B653+B1075+B685,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO

 

[awoohaw]

Does your county to zip code data come like that? The county and zip codes in the same column? Would it be possible for you to have the county name and zip code in two separate columns, and both columns 100% filled in?

 

[niles08]

Does your county to zip code data come like that? The county and zip codes in the same column? Would it be possible for you to have the county name and zip code in two separate columns, and both columns 100% filled in?

No, it doesn't this was just a pivot table I created of the two. I just uploaded another version below that has the zip code and the county that zip code belongs on. Since this isn't a pivot yet, there are just over 10,500 records.

Loading Google Sheets

 

[awoohaw]

Okay, probably the biggest and most complicated formula i've ever created. So, there is probably a much more elegant way.
I copied the data from your google sheet. I placed dummy values in column C.

Then this formula in cell F2:

=LET(
aggregate,HSTACK(
LET(
Countys,$A$2:$A$1120,
Zips,$B$2:$B$1120,
Values,$C$2:$C$1120,
CountyZips,TRANSPOSE(UNIQUE(FILTER(Zips,Countys="ANOKA COUNTY",""))),
matrix,Zips*(CountyZips=Zips),
ZipMatches,BYROW(matrix,LAMBDA(matrix,SUM(matrix))),ZipMatches),
LET(
Countys,$A$2:$A$1120,Zips,$B$2:$B$1120,Values,$C$2:$C$1120,
CountyZips,TRANSPOSE(UNIQUE(FILTER(Zips,Countys="ANOKA COUNTY",""))),
matrix,Zips*(CountyZips=Zips),
ZipMatches,BYROW(matrix,LAMBDA(matrix,SUM(matrix))),
HZipList,TRANSPOSE(FILTER(ZipMatches,ZipMatches>0,"")),
valuematrix,(HZipList=Zips)*Values,
BYROW(valuematrix,LAMBDA(valuematrix,SUM(valuematrix))))),
zipcodes,TAKE(aggregate,,1),
FILTER(aggregate,zipcodes<>0))

Book1
	A	B	C	D	E	F	G	H
1	COUNTY	ZIP
2	ANOKA COUNTY	55038	34			55038	68
3	ANOKA COUNTY	55449	25			55449	25
4	ANOKA COUNTY	55330	25			55330	75
5	ANOKA COUNTY	55303	20			55303	20
6	ANOKA COUNTY	55304	22			55304	22
7	ANOKA COUNTY	55448	33			55448	33
8	ANOKA COUNTY	55011	34			55011	34
9	ANOKA COUNTY	55432	31			55432	31
10	ANOKA COUNTY	55005	38			55005	38
11	ANOKA COUNTY	55014	40			55014	40
12	ANOKA COUNTY	55433	28			55433	28
13	BARNES COUNTY	58481	26			55330	117
14	BARRON COUNTY	54812	36			55038	50
15	BAYFIELD COUNTY	54891	24			55330	72
16	BECKER COUNTY	56554	35
17	BECKER COUNTY	56544	23
18	BECKER COUNTY	56589	33
19	BECKER COUNTY	56501	35
20	BECKER COUNTY	56521	26
Sheet6

Cell Formulas
Range	Formula
F2:G15	F2	=LET(aggregate, HSTACK( LET(Countys,$A$2:$A$1120,Zips,$B$2:$B$1120,Values,$C$2:$C$1120,CountyZips,TRANSPOSE(UNIQUE(FILTER(Zips,Countys="ANOKA COUNTY",""))),matrix,Zips*(CountyZips=Zips),ZipMatches,BYROW(matrix,LAMBDA(matrix,SUM(matrix))),ZipMatches), LET(Countys,$A$2:$A$1120,Zips,$B$2:$B$1120,Values,$C$2:$C$1120,CountyZips,TRANSPOSE(UNIQUE(FILTER(Zips,Countys="ANOKA COUNTY",""))),matrix,Zips*(CountyZips=Zips),ZipMatches,BYROW(matrix,LAMBDA(matrix,SUM(matrix))),HZipList,TRANSPOSE(FILTER(ZipMatches,ZipMatches>0,"")),valuematrix,(HZipList=Zips)*Values, BYROW(valuematrix,LAMBDA(valuematrix,SUM(valuematrix))))), zipcodes,TAKE(aggregate,,1),FILTER(aggregate,zipcodes<>0))
Dynamic array formulas.