count values in one range if the cells in a corresponding range are not empty

[Thurgel]

Hello, I've got stuck and can't find the answer anywhere...

I have a data set from a survey which tells me how many people of different age ranges they have in their household.

In order to calculate the number of households with adults between 16 and 64 and dependents who are under 16, I would like to use a forumula that counts all the instances where there is a value in columns D to G (green in the attached), i.e. each household IF there is a value in the corresponding cells A to B (yellow in the attached).

I'm pretty sure I should be able to do this but have tried all sorts, to no avail.

Here are some of the forumals I've tried:

=IF(D2:G875,"<>"),AND(B2:C875,"<>"),COUNT(D2:G875)

=IF(B2:C875,"<>"),counta(D2:G875)

=countif(D2:G875,"<>"),(B2:C875,"<>")

These all return a 'There's a problem with this formula' errors.

Can anyone help?

Thanks!

[oeldere]

K2 =countif(K2:K20,">0") => is result

K3 =if(SUM(A2:B2)=0,"",SUM(A1:G2)) and drag down.

See the attached file.

[Limor_OP]

There's a little contradiction between what you ask and what your formula shows.
You say "IF there is a value in the corresponding cells A to B"

But your formula is taking B:C as the condition of not being empty.

Anyway if I understand you correct - here is the formula you need to apply:
J2 =IF(AND(A2<>"",B2<>""),SUM(D2:G2),"")

[Thurgel]

I realised that was confusing after posting- the spreadsheet i posted was just a small extract from a much larger dataset I'm working with, and the columns in the formula refer to the larger dataset.

I couldn't get your formula to work. when i paste
=IF(AND(A2<>"",B2<>""),SUM(D2:G2),"")
directly, it doesn't do anything at all (no results and no error message, the formula just sits in the cell).
I tried typing it, but same thing happened.

I played around a little and it brought up a FALSE result but that's seems to be the result regardless of whether there are values in both ranges.

I've attached in case that makes any sense of it.

I think from bost the responses so far I've understood that i need to go line by line and then count up, rather than trying to do all the rows at once.

BTW, I don't want to add up the values, I just want to count each row as 1 (or true) if it has values in a or b AND values in c or d or e or f or g

basically, does the household have any adults between 16-54 AND any children between 0-15

[Thurgel]

I don't want to add up the values, I just want to count each row as 1 (or true) if it has values in a or b AND values in c or d or e or f or g

basically, does the household have any adults between 16-54 AND any children between 0-15 (if so 1 or TRUE, if not 0 or FALSE

Does that make it any clearer?

Thank you!

[oeldere]

Did you tried my formula in #2.

In this example this are 8 familys with childeren <16 (A or B) and a member < 65 year (in C : G)

[Thurgel]

Oh yes! It works, I'm sorry I didn't figure out your logic, but now I have and it does work. Thank you so much!

[Thurgel]

What I need to do now is count adults 16-54 with no children under 16 and no adults over 55.

I've tried to adjust the formula you gave me, but it doesn't seem to be working - are you able to help with this?

Thanks!

[Thurgel]

Don't worry, I've figured it out!

[Thurgel]

actually, it's not working properly.

So what I need is to count every household where there is at one or more child under 16 AND one or more adults 16-54 AND one or more adults over 55

I also need to count hosueholds where there is one or more adults 16-54 AND one or more adults over 55 but NO children under 16

your help greately appreciated as always

[oeldere]

M2 =IF(OR(SUM($A2:$B2)=0,SUM($C2:$G2)=0,SUM($H2:$I2)=0),"","yes")

O2 =IF(OR(SUM($C2:$G2)=0,SUM($H2:$I2)=0),"","yes")

Both results are zero

M1 =Countif(M2:M20,"yes")

O1 =Countif(O2:O20,"yes")


See the attached file.

[Thurgel]

thanks for your reply.

So in the sheet attached I have included column G in the third category (over 55 - blue colour).

To explain a bit more, what I would like for scenario 1:
count every household where there is at one or more child under 16 AND one or more adults 16-54 AND one or more adults over 55
so for exmaple, row 17 in the attached should be counted (and not 0).

For scenario 2
households where there is one or more adults 16-54 AND one or more adults over 55 but NO children under 16

row 11 in the attached should be counted (and not 0)

Does that make sense?

[oeldere]

M2 =IF(OR(SUM($A2:$B2)=0,SUM($C2:$F2)=0,SUM($G2:$I2)=0),"","yes")

O2 = IF(AND(SUM($A2:$B2)=0,SUM($C2:$F2)>0,SUM($G2:$I2)>0),"yes","")


Make sure the answer you give, meet the conditions. (row 11 don't meet the conditions).

See the attached file

[oeldere]

Glad I could help.

Thanks for marking the question solved.