Excel: 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


This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of November 24, 2015.


For more resources for Microsoft Excel