Or to use slightly less esoteric methods to get the values "A", "B", and "C"
to compare against......
1. Using SUMPRODUCT
Define named range Sex as A1:A10
Define named range Grade as B1:B10
To count the number of Male students with a grade of A, enter the
formula =SUMPRODUCT((Sex="M")*(Grade="A")) in the desired cell
To count the number of Female students with grade of B, enter the
formula =SUMPRODUCT((Sex="F")*(Grade="B")) in the desired cell
etc.
----------------------------------------------------------------------------
--------
2. Alternately, count ones and zeros in SUM with a nested IF, entered as
array formulas.
Define named range Sex as A1:A10
Define named range Grade as B1:B10
To count the number of Male students with a grade of A, enter the
formula =SUM(IF(Sex="M",IF(Grade="A",1,0))) in the desired cell
To count the number of Female students with grade of B, enter the
formula =SUM(IF(Sex="F",IF(Grade="B",1,0))) in the desired cell
etc.
Don't forget to use <CTRL><SHIFT><ENTER> to enter the formula as an
array formula (Excel will put little braces around the formula to confirm
it's an array formula)
----------------------------------------------------------------------------
-----------------------------
Variations:
a. Put descriptive labels next to the formulas so you'll know what the
number means. For example the result "4" by itself is not particularly
informative unless you can tell at a glance that's the number of Male
students with a grade of "B". More flexible is to use cell values next to
the final result as the value to compare against -- for example rather than
typing in Sex="M", place the value "M" in cell D14 and use Sex=$D14. You can
then change the results at will by changing the value of a cell rather than
hard coding it in the formula. You can also use those values to build a
string for the label of the final value(s) to make the approach even more
general.
b. You can also add checksums in selected cells to flag some missing
values. For example, if you've entered the named ranges as defined above, a
cell with the the formula:
=MAX(COUNTA(Sex),COUNTA(Grade))
can be used to compare against totals of your sub-categories. In your
example, that formula will give the value of 10. If you then sum the
sub-category reulsts (Males with A + Males with B + Males with C + Females
with A...etc.) and add a text messaage if it doesn't add to 10 -- tells you
one of your two columns has a missing value. Further refinements would use
other cells with counts of F and M values in column A, and counts of A,B,
and C values in column B. That will allow you to more specifically flag
missing or incorrect values (if someone inadvertently put a "Q" instead of M
or F, or entered a grade of "Z"). Not that important if you're dealing with
only 10 sets of values, since you can see all the data at once, but very
useful if you're dealing with more data than you can see on a single screen.
Regards,
Sox
