Sum a range of data considering a range of criteria

[quemuenchatocha]

Dear beast regards

I am working with some figures on the number of people in a country, classified according to sex. I want to carry out sums taking into account specific age groups, for example Men between 2 and 10 years old (Men_2 - Men 10).

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	GENDER	Men_0	Men_1	Men_2	Men_3	Men_4	Men_5	Men_6	Men_7	Men_8	Men_9	Men_10	Men_11	Men_12	Men_13	Men_14	Men_15	Men_16	Men_17	Men_18	Men_19	Men_20
2	TOTAL	389.339	389.438	389.552	389.755	390.127	390.648	391.370	392.272	393.584	395.161	397.190	399.724	402.697	406.195	410.047	413.983	417.904	421.494	424.678	426.956	427.880
3	GENDER	Women_0	Women_1	Women_2	Women_3	Women_4	Women_5	Women_6	Women_7	Women_8	Women_9	Women_10	Women_11	Women_12	Women_13	Women_14	Women_15	Women_16	Women_17	Women_18	Women_19	Women_20
4	TOTAL	371.789	371.891	372.035	372.283	372.657	373.224	373.986	374.956	376.344	378.044	380.150	382.719	385.890	389.509	393.634	398.101	402.694	407.236	411.759	415.629	418.405
5
6		SUMIFS	SUM(SUMIF)	SUMPRODUCT
7	Men: 0 - 12	0	5.110.857	?
8	Women: 0 - 10	0	4.117.359	?
Data

Cell Formulas
Range	Formula
B7	B7	=SUMIFS(B2:V2,B1:V1,"Men_0",B1:V1,"Men_12")
C7	C7	=SUM(SUMIF(B1:V1,{"Men_0";"Men_1";"Men_2";"Men_3";"Men_4";"Men_5";"Men_6";"Men_7";"Men_8";"Men_9";"Men_10";"Men_11";"Men_12"},B2:V2))
B8	B8	=SUMIFS(B4:V4,B3:V3,"Women_0",B3:V3,"Women_10")
C8	C8	=SUM(SUMIF(B3:V3,{"Women_0";"Women_1";"Women_2";"Women_3";"Women_4";"Women_5";"Women_6";"Women_7";"Women_8";"Women_9";"Women_10"},B4:V4))

I have tried with SUMIFS and SUMPRODUCT but I have not been successful in the calculation (I do not know in which aspects I am making mistakes), instead I have tried with SUM(SUMIF) and I have obtained a result, only that it is an operation that is quite extensive, since if I decide to incorporate a very long age range, I would have to write each one of the criteria in the formula, and this would be quite long.

Is there a simpler formula to perform this calculation?

I appreciate your valuable input and comments.

PST: I don't know if it is also possible to use the FILTER function, in order to get the same results as required.

 

[Fluff]

How about

=SUMPRODUCT((MID(B1:V1,FIND("_",B1:V1)+1,100)+0<=MID(A7,FIND("-",A7)+1,100)+0)*(B2:V2))

 

[quemuenchatocha]

How about

=SUMPRODUCT((MID(B1:V1,FIND("_",B1:V1)+1,100)+0<=MID(A7,FIND("-",A7)+1,100)+0)*(B2:V2))

Dear @Fluff thank you very much for your valuable information.

Now a doubt has arisen in my mind and that is, how could I apply the same formula if I want to modify the age range, that is, if I want to find the sum for the age ranges for men between 3 and 10 years old (E1:L1), or 4 and 9 years old (F1:K1) or 11 and 15 years old (M1:Q1), without taking into account (eliminating) the information recorded in cells A7:A8?

Since as you propose it, it takes the values from cell B1 (year 0) and I do not know how to modify it to change this lower limit for another one that I want.

Is there a way to enter these criteria manually in the formula, for example, something like:

=SUMPRODUCT((B1:V1)*(here enter the lower limit of the age range I am looking for <= the upper limit of the age range I am looking for)*(B2:V2)) ?

The only thing I can think of for now is to extract the digit of each of the corresponding cells from row 1 and add them in another row (for example row 5, cell B5) like this:

B5 = VALUE(MID(B2:V2,FIND("_",B2:V2)+1,100))

and once the results are obtained in row 5, apply the SUMPRODUCT function in cell B6, for example, for the age range between 3 and 10 years old like this:

B6 =SUMPRODUCT((B5:V5)*(3 <= 10)*(B2:V2)) = 3.140.107

Obviously, the above formula, as I put it, does not give the correct result.

I hope you understand what I am looking for, and that I am not being somewhat confusing in my explanation.

 

[jtakw]

Hi,

Slightly modify your set up like I show in A6:D8
Enter whatever age range in B7:C8, the SUMPRODUCT will output result accordingly.

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	GENDER	Men_0	Men_1	Men_2	Men_3	Men_4	Men_5	Men_6	Men_7	Men_8	Men_9	Men_10	Men_11	Men_12	Men_13	Men_14	Men_15	Men_16	Men_17	Men_18	Men_19	Men_20
2	TOTAL	389339	389438	389552	389755	390127	390648	391370	392272	393584	395161	397190	399724	402697	406195	410047	413983	417904	421494	424678	426956	427880
3	GENDER	Women_0	Women_1	Women_2	Women_3	Women_4	Women_5	Women_6	Women_7	Women_8	Women_9	Women_10	Women_11	Women_12	Women_13	Women_14	Women_15	Women_16	Women_17	Women_18	Women_19	Women_20
4	TOTAL	371789	371891	372035	372283	372657	373224	373986	374956	376344	378044	380150	382719	385890	389509	393634	398101	402694	407236	411759	415629	418405
5
6	Gender	Age from	Age to	Total
7	Men	3	10	3140107
8	Women	4	9	2249211
Sheet1070

Cell Formulas
Range	Formula
D7	D7	=SUMPRODUCT((MID(B1:V1,FIND("_",B1:V1)+1,9)+0>=B7)*(MID(B1:V1,FIND("_",B1:V1)+1,9)+0<=C7)*B2:V2)
D8	D8	=SUMPRODUCT((MID(B3:V3,FIND("_",B3:V3)+1,9)+0>=B8)*(MID(B3:V3,FIND("_",B3:V3)+1,9)+0<=C8)*B4:V4)

 

[Fluff]

If you want to hard code the age range you can do it like

=SUMPRODUCT((MID(B1:V1,FIND("_",B1:V1)+1,100)+0>=0)*(MID(B1:V1,FIND("_",B1:V1)+1,100)+0<=12)*(B2:V2))

 

[quemuenchatocha]

Hi,

Slightly modify your set up like I show in A6:D8
Enter whatever age range in B7:C8, the SUMPRODUCT will output result accordingly.

Dear Mr. @jtakw, thank you very much for your valuable information and collaboration, is what I'm looking for, you gave me several ideas to modify the formula and adjust it to my needs and required results, a big hug!

 

[quemuenchatocha]

If you want to hard code the age range you can do it like

=SUMPRODUCT((MID(B1:V1,FIND("_",B1:V1)+1,100)+0>=0)*(MID(B1:V1,FIND("_",B1:V1)+1,100)+0<=12)*(B2:V2))

Dear @Fluff, as always your help is very useful and practical, thank you for answering my questions and providing solutions to my concerns, big hug!

 

[jtakw]

You're welcome.

 

[Fluff]

Glad to help & thanks for the feedback