Opposite of Countifs

[Steve]

I have two columns of data. One contains the number of adults per household
and the other contains the number of children. The rest of the sheet is
filled out with survey data for my thesis.

I’m using countifs and averageifs to analyze data for households that have
at least one adult and one child.

Is there a formula that returns the data that doesn’t pass the if statement?
I need to count and average the households that have less than one adult
and/or less than 1 child.

Any suggestions?

 

[Ron Rosenfeld]

I have two columns of data. One contains the number of adults per household
and the other contains the number of children. The rest of the sheet is
filled out with survey data for my thesis.

I’m using countifs and averageifs to analyze data for households that have
at least one adult and one child.

Is there a formula that returns the data that doesn’t pass the if statement?
I need to count and average the households that have less than one adult
and/or less than 1 child.

Any suggestions?

The count would simply be the count of households less the value you get in
your countifs formula above:

e.g. =COUNTA(Household)-COUNTIFS(AdultCount,">0",ChildCount,">0")

I'm not sure from your explanation what it is that you are averaging, so you'll
have to be more specific.

You might be able to use the above count formula as the divisor, and sum up
whatever it is you are interested in averaging.


--ron

 

[Ashish Mathur]

Hi,

=sumproduct((rangeA>=1)*(rangeB=1)) - RangeA has number of adults and RangeB
has number of children

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com