Counting 2 different pieces of data in 2 different columns

  • Thread starter Thread starter Cindyt
  • Start date Start date
C

Cindyt

Hi -

This should be so simple - but I can't figure it out

I need to do a countif in one column to count, for example, # of "auto
quotes" and in another column count the number of "sold" that corresponds to
the first column. Ideally, I could figure the percentage along with it, but
if not, I can always do that in a separate cell.

Thanks for any help!
Cindyt
 
If I understand what you want...

=SUMPRODUCT(--(A1:A10="auto quote"),--(B1:B10="sold"))

Better to use cells to hold the criteria...

D1 = auto quote
E1 = sold

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1))
 
Thanks - worked great!

T. Valko said:
If I understand what you want...

=SUMPRODUCT(--(A1:A10="auto quote"),--(B1:B10="sold"))

Better to use cells to hold the criteria...

D1 = auto quote
E1 = sold

=SUMPRODUCT(--(A1:A10=D1),--(B1:B10=E1))
Click to expand...
 
I try t he same thing but it doesn't work and gives me #NUM!

I want to count the entries from that satifies bothe conditions
b1 from column B and c1 from column c

Thanks
 
Ok, there are two possible causes for the #NUM! error.

1. You already have #NUM! errors in one or both ranges

2. You're trying to use entire columns as range references and you're not
usng Excel 2007. If you're not using Excel 2007 you can't do something like
this:

=SUMPRODUCT(--(A:A="x"),--(B:B="y"))

Use smaller specific ranges like:

=SUMPRODUCT(--(A1:A10="x"),--(B1:B10="y"))

If you absolutely need to reference the whole column you can reference the
whole column minus one cell. Like this:

=SUMPRODUCT(--(A1:A65535="x"),--(B1:B65535="y"))

Or
=SUMPRODUCT(--(A2:A65536="x"),--(B2:B65536="y"))
 
Back
Top