Help with Sumifs

[Alex11]

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

 

[Alex11]

Just to clarify

In terms of the names ranges it would look something like

www.datainterrogation.com/temp/names-formula.jpg

www.datainterrogation.com/temp/formula.jpg

 

[Alex11]

The previous images were too small to be helpful.

Apologies

 

[MarkReddell]

Hi Alex! Are you wanting to add the total occurrences by names combined in all states??? And, is all your data in different columns or in just one col.?

 

[Alex11]

Yes Mark

All the data from the separate sheets apply to one State only.

All the columns of the various sheets are the same with the exception of number of rows.

I created a list of unique names from all the states, therefore not every sheet will contain every single name.

I am trying to combine the data and sum the numbers indicated by the data source from all the different States into one sheet.

The matrix will have names going down and years going across.

I will run the same test three times for the sexes and a total.

Once I have all of this I can resort work with male , female and totals.

So what I want to achieve is have one sheet giving the total number of registered first names in the USA by year.

 

[Aladin Akyurek]

Yes Mark

All the data from the separate sheets apply to one State only.

All the columns of the various sheets are the same with the exception of number of rows.

I created a list of unique names from all the states, therefore not every sheet will contain every single name.

I am trying to combine the data and sum the numbers indicated by the data source from all the different States into one sheet.

The matrix will have names going down and years going across.

I will run the same test three times for the sexes and a total.

Once I have all of this I can resort work with male , female and totals.

So what I want to achieve is have one sheet giving the total number of registered first names in the USA by year.

Mister Alex is here an example what I want rom you.

Sum A2:A10.
Test that B2:B10 is M (male)
Test that C2:C10 is London

Etc.

Please try to follow the above manner of providing the requested information involving your own data.

 

[Alex11]

AK M 2014 Spencer 5
AK M 2014 Thaddeus 5
AK M 2014 Tobias 5
AK M 2014 Tyson 5
AK M 2014 Walker 5

413241 =SUM(N1:N26663)

2014
Tobias 5 =SUMIFS($N$1:$N$26663,$M$1:$M$26663,$L26668,$L$1:$L$26663,M$26667,$K$1:$K$26663,"M")

 

[Alex11]

F 1910 Mary 14
F 1910 Annie 12
F 1910 Anna 10
F 1910 Margaret 8
F 1910 Helen 7
F 1910 Elsie 6
F 1910 Lucy 6
F 1910 Dorothy 5
F 1911 Mary 12
F 1911 Margaret 7

SUM 87

1910 1911
Anna 10 0
Annie 12 0
Dorothy 5 0
Elsie 6 0
Helen 7 0
Lucy 6 0
Margaret 8 7
Mary 14 12


Sum 68 19
Total 87

=SUMIFS($U$1:$U$10,$T$1:$T$10,$R15,$S$1:$S$10,1910,$R$1:$R$10,"F")
=SUMIFS($U$1:$U$10,$T$1:$T$10,$R15,$S$1:$S$10,1911,$R$1:$R$10,"F")

 

[Alex11]

Mister Alex is here an example what I want rom you.

Sum A2:A10.
Test that B2:B10 is M (male)
Test that C2:C10 is London

Etc.

Please try to follow the above manner of providing the requested information involving your own data.

Aladin

I think I have provided the information you required

Applying a sum formula to every individual sumifs formula will probably provide the correct result, however, it is a long formula and I want to apply it to a very long list of names across many years.

Is my approach the best way of doing this.

Do you have any suggestions to get my result in a more economical way?

Thank you

 

[Aladin Akyurek]

Aladin

I think I have provided the information you required

Applying a sum formula to every individual sumifs formula will probably provide the correct result, however, it is a long formula and I want to apply it to a very long list of names across many years.

Is my approach the best way of doing this.

Do you have any suggestions to get my result in a more economical way?

Thank you

You mentioned multiple sheets; you even listed three of them: AR, AZ, and CA.

If these sheets share the same lay-out, you can have in a separate sheet the following...

Create a range which houses the names of the relevant sheets, select this range, and name the selection SheetList.

You can now invoke something like:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SheetList&"'!A:A"),INDIRECT("'"&SheetList&"'!B:B"),$E2,INDIRECT("'"&SheetList&"'!C:C"),$F2))

where column A ofeach sheet gets summed when column B of each sheet is equal to E2 and column C to F2.

Adjust to suit.

I apologize if your question has nothing to do with my guess at understanding of it.