Can I do a Double CountIF Statement

  • Thread starter Thread starter JEFF
  • Start date Start date
J

JEFF

I need to do a count of items in two columns. For
example, If I had a column of hair color and a column of
gender, how can I count the number of red haired females
in the given range?
 
Suppose hair color resides in column A and gender in
column B (given as "f" or "m"). Use:

=SUMPRODUCT((A1:A100="red")*(B1:B100="f"))

HTH
Jason
Atlanta, GA
 
Try this:

=sum(if((b2:b100="Red")*(c2:c100="Female")),1,0)

Use ctrl+shift+enter to make it an array formula.

Jef
 
Hi JEFF!

In addition to the formulas given:

=SUMPRODUCT(--($A$1:$A$100=$G2),--($B$1:$B$100=H$1))

Note that I've made the range of your data absolute and I've put the
Hair Colour in G2 and the Gender in H1

The structures ($A$1:$A$100) and ($B$1:$B$100=H$1) are implicit IF
functions that return TRUE or FALSE. We use the -- to coerce these
returns to 1 and 0. Only if a pair of cells (eg) A7 and B7 are both
TRUE will the product be 1 (1*1=1; 0*1=0; 1*0=0) and cause the sum of
the products to increment by 1.

With the formula in H2 you can copy down and across and it will
provide counts for Black in (say) G3, Blonde in (say) G4, Brunette in
(say) G5 with Gender Male in I1. Careful use of referencing has
allowed the formula to become much more powerful.

You might also use this data as something that will be a good
introduction to Pivot Tables:

See:
Jon Peltier hosted, Debra Dalgleish written:
http://peltiertech.com/Excel/Pivots/pivotstart.htm


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top