|
count across 3 columns
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 |
count across 3 columns
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:O100 0<"")) SUMPRODUCT() is a powerful feature! See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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 |
count across 3 columns
Hi,
thanks for your help. Males with special needs works perfectly. I entered =SUMPRODUCT((B21:B153="m")*(M21:M153<"")*(O21:O15 3<"")) 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($N210,LOOKUP($N21,AR$20:$AR$62,AS$20:AS$62)," ") is that the problem? Thanks Mrs T "Gary''s Student" wrote: 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:O100 0<"")) SUMPRODUCT() is a powerful feature! See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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 |
count across 3 columns
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." wrote: Hi, thanks for your help. Males with special needs works perfectly. I entered =SUMPRODUCT((B21:B153="m")*(M21:M153<"")*(O21:O15 3<"")) 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($N210,LOOKUP($N21,AR$20:$AR$62,AS$20:AS$62)," ") is that the problem? Thanks Mrs T "Gary''s Student" wrote: 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:O100 0<"")) SUMPRODUCT() is a powerful feature! See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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 |
count across 3 columns
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:O15 3<"")) gives me an answer of #N/A (boys with special needs and a test score). Thanks for your help Mrs T "Francis" wrote: 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." wrote: Hi, thanks for your help. Males with special needs works perfectly. I entered =SUMPRODUCT((B21:B153="m")*(M21:M153<"")*(O21:O15 3<"")) 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($N210,LOOKUP($N21,AR$20:$AR$62,AS$20:AS$62)," ") is that the problem? Thanks Mrs T "Gary''s Student" wrote: 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:O100 0<"")) SUMPRODUCT() is a powerful feature! See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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 |
count across 3 columns
The SUMPRODUCT() function is examining cols B, M, and O.
There can be no errors in these three columns for SUMPRODUCT(_) to work. -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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:O15 3<"")) gives me an answer of #N/A (boys with special needs and a test score). Thanks for your help Mrs T "Francis" wrote: 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." wrote: Hi, thanks for your help. Males with special needs works perfectly. I entered =SUMPRODUCT((B21:B153="m")*(M21:M153<"")*(O21:O15 3<"")) 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($N210,LOOKUP($N21,AR$20:$AR$62,AS$20:AS$62)," ") is that the problem? Thanks Mrs T "Gary''s Student" wrote: 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:O100 0<"")) SUMPRODUCT() is a powerful feature! See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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 |
count across 3 columns
Try this:
=SUMPRODUCT(--(B21:B153="m"),--(M21:M153<""),--(ISNUMBER(O21:O153))) -- Biff Microsoft Excel MVP "Gary''s Student" wrote in message ... The SUMPRODUCT() function is examining cols B, M, and O. There can be no errors in these three columns for SUMPRODUCT(_) to work. -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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:O15 3<"")) gives me an answer of #N/A (boys with special needs and a test score). Thanks for your help Mrs T "Francis" wrote: 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." wrote: Hi, thanks for your help. Males with special needs works perfectly. I entered =SUMPRODUCT((B21:B153="m")*(M21:M153<"")*(O21:O15 3<"")) 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($N210,LOOKUP($N21,AR$20:$AR$62,AS$20:AS$62)," ") is that the problem? Thanks Mrs T "Gary''s Student" wrote: 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:O100 0<"")) SUMPRODUCT() is a powerful feature! See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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 |
count across 3 columns
Brilliant. Thank you...again!!!
Mrs T :) "T. Valko" wrote: Try this: =SUMPRODUCT(--(B21:B153="m"),--(M21:M153<""),--(ISNUMBER(O21:O153))) -- Biff Microsoft Excel MVP "Gary''s Student" wrote in message ... The SUMPRODUCT() function is examining cols B, M, and O. There can be no errors in these three columns for SUMPRODUCT(_) to work. -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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:O15 3<"")) gives me an answer of #N/A (boys with special needs and a test score). Thanks for your help Mrs T "Francis" wrote: 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." wrote: Hi, thanks for your help. Males with special needs works perfectly. I entered =SUMPRODUCT((B21:B153="m")*(M21:M153<"")*(O21:O15 3<"")) 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($N210,LOOKUP($N21,AR$20:$AR$62,AS$20:AS$62)," ") is that the problem? Thanks Mrs T "Gary''s Student" wrote: 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:O100 0<"")) SUMPRODUCT() is a powerful feature! See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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 |
count across 3 columns
You're welcome!
-- Biff Microsoft Excel MVP "Mrs T." wrote in message ... Brilliant. Thank you...again!!! Mrs T :) "T. Valko" wrote: Try this: =SUMPRODUCT(--(B21:B153="m"),--(M21:M153<""),--(ISNUMBER(O21:O153))) -- Biff Microsoft Excel MVP "Gary''s Student" wrote in message ... The SUMPRODUCT() function is examining cols B, M, and O. There can be no errors in these three columns for SUMPRODUCT(_) to work. -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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:O15 3<"")) gives me an answer of #N/A (boys with special needs and a test score). Thanks for your help Mrs T "Francis" wrote: 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." wrote: Hi, thanks for your help. Males with special needs works perfectly. I entered =SUMPRODUCT((B21:B153="m")*(M21:M153<"")*(O21:O15 3<"")) 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($N210,LOOKUP($N21,AR$20:$AR$62,AS$20:AS$62)," ") is that the problem? Thanks Mrs T "Gary''s Student" wrote: 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:O100 0<"")) SUMPRODUCT() is a powerful feature! See: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Gary''s Student - gsnu200851 "Mrs T." wrote: 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 |
| All times are GMT +1. The time now is 04:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com