| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to count on two columns | Excel Worksheet Functions | |||
| Count of Different Columns | Excel Discussion (Misc queries) | |||
| How to count for 3 columns | Excel Worksheet Functions | |||
| Count in two columns | Excel Worksheet Functions | |||
| Count certain changes between columns | Excel Worksheet Functions | |||
![Mrs T.[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
