SUMIFS Error??

[greenswan]

I've been stuck on this formula for like 8 hours and I am dying to solve it. I'm dealing with a household dataset from the Demographic and Healthcare Survey (DHS) with unique identifiers and household characteristics on the same row.


HHID = household id


SEX1 = sex of household member 1


AGE1 = age of household member 2


EDU1 = years of schooling obtained by household member 1


Example:


[TABLE="width: 500"]
<tbody>[TR]
[TD]HHID[/TD]
[TD]SEX1[/TD]
[TD]SEX2[/TD]
[TD]SEX3[/TD]
[TD]SEX4[/TD]
[TD]AGE1[/TD]
[TD]AGE2[/TD]
[TD]AGE3[/TD]
[TD]AGE4[/TD]
[TD]EDU1[/TD]
[TD]EDU2[/TD]
[TD]EDU3[/TD]
[TD]EDU4[/TD]
[/TR]
[TR]
[TD]1.1[/TD]
[TD]M[/TD]
[TD]F[/TD]
[TD]M[/TD]
[TD]F[/TD]
[TD]26[/TD]
[TD]30[/TD]
[TD]34[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1.2[/TD]
[TD]F[/TD]
[TD]F[/TD]
[TD][/TD]
[TD][/TD]
[TD]40[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




I would like to create a single column which calculates the average years of schooling obtained by men and women over the age of 25. For example, Household 1.1 would have 6 for men and 8 for women. Household 1.2 would have be blank for men and have 8 for women.


These individual formulas work:


SUMIF(B3:AM3,"*"&$DK$1&"*",BZ3:DJ3)


and


SUMIF(AN3:BY3,"<24",BZ3:DJ3)


but I can't get the combination to work:


=SUMIFS(BZ3:DJ3,B3:AM3,"*"&$DK$1&"*",AN3:BY3,"<24")


For reference:


BZ3:DJ3 = Educational attainment columns


B3:AM3 = Gender columns (M/F)


DK1 = M


AN3:BY3 = Age columns




Can anyone help? I'm really losing the will to live!!


Thanks in advance

 

[Marcelo Branco]

Maybe something like this (adjust the ranges according to your actual scenario)
Observe that the 3 ranges must have the same size


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[TD="bgcolor: #DCE6F1"]

L​

[/TD]
[TD="bgcolor: #DCE6F1"]

M​

[/TD]
[TD="bgcolor: #DCE6F1"]

N​

[/TD]
[TD="bgcolor: #DCE6F1"]

O​

[/TD]
[TD="bgcolor: #DCE6F1"]

P​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

HHID​

[/TD]
[TD]

SEX1​

[/TD]
[TD]

SEX2​

[/TD]
[TD]

SEX3​

[/TD]
[TD]

SEX4​

[/TD]
[TD]

AGE1​

[/TD]
[TD]

AGE2​

[/TD]
[TD]

AGE3​

[/TD]
[TD]

AGE4​

[/TD]
[TD]

EDU1​

[/TD]
[TD]

EDU2​

[/TD]
[TD]

EDU3​

[/TD]
[TD]

EDU4​

[/TD]
[TD][/TD]
[TD]

M​

[/TD]
[TD]

F
​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

1.1​

[/TD]
[TD]

M​

[/TD]
[TD]

F​

[/TD]
[TD]

M​

[/TD]
[TD]

F​

[/TD]
[TD]

26​

[/TD]
[TD]

30​

[/TD]
[TD]

34​

[/TD]
[TD]

12​

[/TD]
[TD]

8​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD]

6​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1.2​

[/TD]
[TD]

F​

[/TD]
[TD]

F​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

40​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

8​

[/TD]
[/TR]
</tbody>[/TABLE]


Type M and F (criteria) in O1 and P1
Formula in O2 copied across and down
=IFERROR(AVERAGEIFS($J2:$M2,$B2:$E2,O$1,$F2:$I2,">25"),"")

Hope this helps

M.

 

[greenswan]

Thank you so much, Marcelo!

I adjusted the formula to (AVERAGEIFS($J2:$M2,$B2:$E2,"*"&O$1&"*",$F2:$I2,">25") and it worked perfectly

Have a nice day!