Opposite of Countifs
 

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.

Im 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 doesnt 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?

Opposite of Countifs
 

On Sat, 10 Oct 2009 15:43:01 -0700, Steve
wrote:

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

Opposite of Countifs
 

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

"Steve" wrote in message
...
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.

Im 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 doesnt 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?