| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding letters instead of numbers and an additonal criteria
ive got a column with grades ranging from A-G. i am trying to find out how
many A-C grades there are. A user has helped me out with the following formula "Jacob Skaria" wrote: SUM(COUNTIF(enla,{"A","B","C"})) where enla is my named range. What if i wanted to break this down further and wanted to see how many males got A-C grades. I think i'd have to use sumproduct, but don't know how to compose this. Please could you help? many thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding letters instead of numbers and an additonal criteria
Try the below with "m" for male in ColB
=SUMPRODUCT(--(B1:B100="m"),--ISNUMBER(MATCH(A1:A100,{"a","b","c"},0))) If this post helps click Yes --------------- Jacob Skaria "mits" wrote: ive got a column with grades ranging from A-G. i am trying to find out how many A-C grades there are. A user has helped me out with the following formula "Jacob Skaria" wrote: SUM(COUNTIF(enla,{"A","B","C"})) where enla is my named range. What if i wanted to break this down further and wanted to see how many males got A-C grades. I think i'd have to use sumproduct, but don't know how to compose this. Please could you help? many thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Adding letters instead of numbers and an additonal criteria
=SUMPRODUCT(--(B1:B100="m"),--ISNUMBER(MATCH(A1:A100,{"a","b","c"},0)))
shorter formula: =SUMPRODUCT((B1:B100="m")*(A1:A100={"a","b","c"})) "Jacob Skaria" wrote: Try the below with "m" for male in ColB =SUMPRODUCT(--(B1:B100="m"),--ISNUMBER(MATCH(A1:A100,{"a","b","c"},0))) If this post helps click Yes --------------- Jacob Skaria "mits" wrote: ive got a column with grades ranging from A-G. i am trying to find out how many A-C grades there are. A user has helped me out with the following formula "Jacob Skaria" wrote: SUM(COUNTIF(enla,{"A","B","C"})) where enla is my named range. What if i wanted to break this down further and wanted to see how many males got A-C grades. I think i'd have to use sumproduct, but don't know how to compose this. Please could you help? many thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding letters instead of numbers | Excel Worksheet Functions | |||
| adding numbers with letters | Excel Worksheet Functions | |||
| adding numbers but not letters | Excel Worksheet Functions | |||
| adding numbers with letters | Excel Discussion (Misc queries) | |||
| Adding or removing additonal links | Excel Discussion (Misc queries) | |||
Hybrid Mode
