Count function

[Jerry Edge]

I have a spredsheet with 10 codes and a database with a list of data with
three columns one is headed code, (same as the 10 codes), one headed Race and
one headed Gender. I am truing to count by race and by gender and by code.
For example, I am trying to count all of the Hispanics that are also males
that fit into clde 2.

 

[Shane Devenshire]

Hi,

Your discription suggest that the quickest and most elegent solution might
be a pivot table.

If you want a non-pivot table solution in 2007 you could consider
=COUNTIFS(Race,"Hispanics",Gender,"Male")
is an example, where Race is the column containing race and Gender is the
range containing the gender. You can expand this to handle three criteria at
the same time if necessary.

In 2003 you will need something like
=SUMPROODUCT((Race="Hispancis")*(Gender="Female"))

 

[Mike H]

Hi,

Maybe this

=SUMPRODUCT((B2:B20="Hispanic")*(C2:C20="Male")*(A2:A20=2))

In practice I'd use cell references

=SUMPRODUCT((B2:B20=D1)*(C2:C20=E1)*(A2:A20=F1))

Mike

 

[Jerry Edge]

i tried all of the suggestions but they did not work either. I have a chart
i am trying to complete with data from a different worksheet. Mu chart is on
one worksheet and the data is on another worksheet.

Here is what I put in the calculation line
=SUMPRODUCT((Data!C:C=B9)*(Data!D=Control!A4)*(Data!E:E=Control!A9),0)

I get this symbol as an answer #NUM!

Jerry