Help with Sumifs

Good Day

I am busy with an ambitious project. I have obtained data of all first names registered in al US states from 1910 to 2014.

The data is simply too much to be able to use one sheet only.

What I have done is to have all the 51 states in its own sheet.

The data contains names, sex, year and total count of names.

The sheets are sorted by year and name occurrences


I created a unique list of all the names in a new sheet and created a matrix with names running down and years running across.

The unique number of names is a list of 30255 first names sorted alphabetically.

I created named ranges as follows for all states from the sheets:

Wyoming WY WY_Sex WY_Year WY_Name WY_No
West Virginia WV WV_Sex WV_Year WV_Name WV_No
Wisconsin WI WI_Sex WI_Year WI_Name WI_No

etc.



I tried to populate my matrix with the following formula:

=SUM(SUMIFS(WY_No,WY_Name,$B20,WY_Year,E$18,WY_Sex,M),SUMIFS(WV_No,WV_Name,$B20,WV_Year,E$18,WV_Sex,M),SUMIFS(WI_No,WI_Name,$B20,WI_Year,E$18,WI_Sex,M),SUMIFS(WA_No,WA_Name,$B20,WA_Year,E$18,WA_Sex,M),SUMIFS(VT_No,VT_Name,$B20,VT_Year,E$18,VT_Sex,M),SUMIFS(VA_No,VA_Name,$B20,VA_Year,E$18,VA_Sex,M),SUMIFS(UT_No,UT_Name,$B20,UT_Year,E$18,UT_Sex,M),SUMIFS(TX_No,TX_Name,$B20,TX_Year,E$18,TX_Sex,M),SUMIFS(TN_No,TN_Name,$B20,TN_Year,E$18,TN_Sex,M),SUMIFS(SD_No,SD_Name,$B20,SD_Year,E$18,SD_Sex,M),SUMIFS(SC_No,SC_Name,$B20,SC_Year,E$18,SC_Sex,M),SUMIFS(RI_No,RI_Name,$B20,RI_Year,E$18,RI_Sex,M),SUMIFS(PA_No,PA_Name,$B20,PA_Year,E$18,PA_Sex,M),SUMIFS(OR_No,OR_Name,$B20,OR_Year,E$18,OR_Sex,M),SUMIFS(OH_No,OH_Name,$B20,OH_Year,E$18,OH_Sex,M),SUMIFS(NY_No,NY_Name,$B20,NY_Year,E$18,NY_Sex,M),SUMIFS(NV_No,NV_Name,$B20,NV_Year,E$18,NV_Sex,M),SUMIFS(NM_No,NM_Name,$B20,NM_Year,E$18,NM_Sex,M),SUMIFS(NJ_No,NJ_Name,$B20,NJ_Year,E$18,NJ_Sex,M),SUMIFS(NH_No,NH_Name,$B20,NH_Year,E$18,NH_Sex,M),SUMIFS(NE_No,NE_Name,$B20,NE_Year,E$18,NE_Sex,M),SUMIFS(ND_No,ND_Name,$B20,ND_Year,E$18,ND_Sex,M),SUMIFS(NC_No,NC_Name,$B20,NC_Year,E$18,NC_Sex,M),SUMIFS(MT_No,MT_Name,$B20,MT_Year,E$18,MT_Sex,M),SUMIFS(MS_No,MS_Name,$B20,MS_Year,E$18,MS_Sex,M),SUMIFS(MO_No,MO_Name,$B20,MO_Year,E$18,MO_Sex,M),SUMIFS(MN_No,MN_Name,$B20,MN_Year,E$18,MN_Sex,M),SUMIFS(MI_No,MI_Name,$B20,MI_Year,E$18,MI_Sex,M),SUMIFS(ME_No,ME_Name,$B20,ME_Year,E$18,ME_Sex,M),SUMIFS(MD_No,MD_Name,$B20,MD_Year,E$18,MD_Sex,M),SUMIFS(MA_No,MA_Name,$B20,MA_Year,E$18,MA_Sex,M),SUMIFS(LA_No,LA_Name,$B20,LA_Year,E$18,LA_Sex,M),SUMIFS(KY_No,KY_Name,$B20,KY_Year,E$18,KY_Sex,M),SUMIFS(KS_No,KS_Name,$B20,KS_Year,E$18,KS_Sex,M),SUMIFS(IN_No,IN_Name,$B20,IN_Year,E$18,IN_Sex,M),SUMIFS(IL_No,IL_Name,$B20,IL_Year,E$18,IL_Sex,M),SUMIFS(ID_No,ID_Name,$B20,ID_Year,E$18,ID_Sex,M),SUMIFS(IA_No,IA_Name,$B20,IA_Year,E$18,IA_Sex,M),SUMIFS(HI_No,HI_Name,$B20,HI_Year,E$18,HI_Sex,M),SUMIFS(GA_No,GA_Name,$B20,GA_Year,E$18,GA_Sex,M),SUMIFS(FL_No,FL_Name,$B20,FL_Year,E$18,FL_Sex,M),SUMIFS(DE_No,DE_Name,$B20,DE_Year,E$18,DE_Sex,M),SUMIFS(DC_No,DC_Name,$B20,DC_Year,E$18,DC_Sex,M),SUMIFS(CT_No,CT_Name,$B20,CT_Year,E$18,CT_Sex,M),SUMIFS(CO_No,CO_Name,$B20,CO_Year,E$18,CO_Sex,"M"),SUMIFS(CA_No,CA_Name,$B20,CA_Year,E$18,CA_Sex,M),SUMIFS(AZ_No,AZ_Name,$B20,AZ_Year,E$18,AZ_Sex,M),SUMIFS(AR_No,AR_Name,$B20,AR_Year,E$18,AR_Sex,M),SUMIFS(AL_No,AL_Name,$B20,AL_Year,E$18,AL_Sex,M),SUMIFS(AK_No,AK_Name,$B20,AK_Year,E$18,AK_Sex,M))

Where $B? is the first name and ?$18 is the year

The process of using this formula on 2014 -2009 took a very long time and did not populate correctly.

Please advise on any suggestions on a more practical approach or a possible reason why the formulas did not work.

Thank you
Alex