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:

HHID	SEX1	SEX2	SEX3	SEX4	AGE1	AGE2	AGE3	AGE4	EDU1	EDU2	EDU3	EDU4
1.1	M	F	M	F	26	30	34	12	8	5	4	6
1.2	F	F			40	10			8	5

<tbody>
</tbody>

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

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

O​

P​

1​

HHID​

SEX1​

SEX2​

SEX3​

SEX4​

AGE1​

AGE2​

AGE3​

AGE4​

EDU1​

EDU2​

EDU3​

EDU4​

M​

F ​

2​

1.1​

M​

F​

M​

F​

26​

30​

34​

12​

8​

5​

4​

6​

6​

5​

3​

1.2​

F​

F​

40​

10​

8​

5​

8​

<tbody>
</tbody>

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!