ExcelBanter - Adding letters instead of numbers and an additonal criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Adding letters instead of numbers and an additonal criteria (https://www.excelbanter.com/excel-worksheet-functions/238963-adding-letters-instead-numbers-additonal-criteria.html)

mits

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

Jacob Skaria

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

Teethless mama

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

All times are GMT +1. The time now is 12:46 PM.