Using Countif

[Al]

Ok, On Tab A I have my raw data that has three columns I'm interested in:

Name
product
score

I'm breaking the info down in Tab B.

I have a countif set up already to tell me how many of any given "name" or
"product" there are in their respective columns. How would I tell it to
count all instances of a specific "score" value(s) associated with a "name"
or "product" though?

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(Sheet1!A1:A100="name"),--(Sheet1!
B1:B100="product"),--(Sheet1!C1:C100="score"))

Biff

 

[Al]

Hmm... Not quite. Allow to ellaborate. In tab B I have colums for name,
total instances of that name, and two columns based on the score. In one
column I want all instances of the highest score, 10, and in the other I want
all results of 8 and above. I'm doing this twice. Once based on name, once
based on product. Hope this clarifies and I do appreciate the help.

 

[Biff]

Hi!

Let me see if I understand, you want to count the number
of times that 10 corresponds with a name and count the
number of times that a value >=8 corresponds to a name?

Try these:

=SUMPRODUCT(--(Sheet1!A1:A100="name"),--(Sheet1!
B1:B100=10))

=SUMPRODUCT(--(Sheet1!A1:A100="name"),--(Sheet1!
B1:B100>=8))

Biff

 

[Al]

Ok that makes sense but what purpose do the "--" operators perform?

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation.

--

HTH

RP
(remove nothere from the email address if mailing direct)