COUNT in either or A or B but not a total -

[NICKRENN]

Hi

I'm trying to count the number of families with a school age child. So i have a column A for boys over 5 under 15 and Column B girls over 5 under 15.

I don't want to manually add in a column that does a calculation to the data set, but i need to know the number of families who have a school age child. Either an entry in column A or B or Both.

So if there are 4 families with boys and 3 with girls - normally count function would return 7 but one of the families has a girl and a boy so the answer i'm looking for is 6.

Is it possible to use count for this? or do i need something else?

Thanks

Nick

 

[barry houdini]

Try like this

=SUMPRODUCT((A2:A100+B2:B100>0)+0)

 

[NICKRENN]

Try like this

=SUMPRODUCT((A2:A100+B2:B100>0)+0)

Thats giving me a #Value error - i've reformatted the cells from General to number and still same error.

my actual formula now reads like this:

=SUMPRODUCT(('HH MSNA'!AH2:AH1000+'HH MSNA'!AI2:AI1000>0)+0)

but i only have data in up to row 30 for this sample.

any ideas?

thanks

 

[admiral100]

Hi,

If the cells conatin text you can try this : (change range as needed)

=SUMPRODUCT(1/(COUNTIF($A$2:$B$10,$A$2:$B$10)),--($A$2:$B$10=A2:B10))

 

[NICKRENN]

Ignore me, i found a cell with text i had missed, that works perfectly now.

thanks so much for your help.

nick

 

[RoryA]

What exactly is in columns A and B? If it's the names, try something like this:

=SUMPRODUCT((('HH MSNA'!AH2:AH1000<>"")+('HH MSNA'!AI2:AI1000<>"")>0)+0)