count across 3 columns

[Mrs T.]

In my test sheet, column B is male(m) or female (f), column M is special
needs (which can be any text) and column O is test score. How can I count
e.g. how many boys have special needs and how many boys with special needs
have a test score? Am tearing my hair out!
Mrs T

 

[Gary''s Student]

Males with special needs:

=SUMPRODUCT((B1:B1000="m")*(M1:M1000<>""))

Males with special needs and having a test score.

=SUMPRODUCT((B1:B1000="m")*(M1:M1000<>"")*(O1:O1000<>""))

SUMPRODUCT() is a powerful feature! See:


http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Mrs T.]

Hi,
thanks for your help. Males with special needs works perfectly. I entered
=SUMPRODUCT((B21:B153="m")*(M21:M153<>"")*(O21:O153<>"")) for males with
special needs and a test score but got #N/A in the cell. Formula that brings
test scores into the sheet is
=IF($N21>0,LOOKUP($N21,AR$20:$AR$62,AS$20:AS$62),"") is that the problem?
Thanks
Mrs T

 

[Francis]

Hi

Does your formula return a result?
what does AR20: AR62 and AS20: AS62 contain?

I suggest that you post a sample on before and after result.
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Mrs T.]

Hi,
This is a lookup table where AR20:AR62 contains levels e.g. 3c, 3b, 3a and
AS20:AS62 contains a value for each level 1,2,3,etc. Column N in my sheet
contains the level and column O brings over the point value for each level
but returns #N/A if a level has not been entered.
Boys with special needs works fine so it must be the last bracket that needs
changing but I don't know what to!
=SUMPRODUCT((B21:B153="m")*(M21:M153<>""))
gives me an answer of 2 (boys with special needs)
=SUMPRODUCT((B21:B153="m")*(M21:M153<>"")*(O21:O153<>""))
gives me an answer of #N/A (boys with special needs and a test score).

Thanks for your help
Mrs T

 

[Gary''s Student]

The SUMPRODUCT() function is examining cols B, M, and O.

There can be no errors in these three columns for SUMPRODUCT(_) to work.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(B21:B153="m"),--(M21:M153<>""),--(ISNUMBER(O21:O153)))

 

[Mrs T.]

Brilliant. Thank you...again!!!
Mrs T

Try this:

=SUMPRODUCT(--(B21:B153="m"),--(M21:M153<>""),--(ISNUMBER(O21:O153)))

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Brilliant. Thank you...again!!!
Mrs T