Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Countif
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? |
#2
|
|||
|
|||
Hi!
Try this: =SUMPRODUCT(--(Sheet1!A1:A100="name"),--(Sheet1! B1:B100="product"),--(Sheet1!C1:C100="score")) Biff -----Original Message----- 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? . |
#3
|
|||
|
|||
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" wrote: Hi! Try this: =SUMPRODUCT(--(Sheet1!A1:A100="name"),--(Sheet1! B1:B100="product"),--(Sheet1!C1:C100="score")) Biff -----Original Message----- 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? . |
#4
|
|||
|
|||
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 -----Original Message----- 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" wrote: Hi! Try this: =SUMPRODUCT(--(Sheet1!A1:A100="name"),--(Sheet1! B1:B100="product"),--(Sheet1!C1:C100="score")) Biff -----Original Message----- 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? . . |
#5
|
|||
|
|||
Ok that makes sense but what purpose do the "--" operators perform?
"Biff" wrote: 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 -----Original Message----- 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" wrote: Hi! Try this: =SUMPRODUCT(--(Sheet1!A1:A100="name"),--(Sheet1! B1:B100="product"),--(Sheet1!C1:C100="score")) Biff -----Original Message----- 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? . . |
#6
|
|||
|
|||
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for an explanation.
-- HTH RP (remove nothere from the email address if mailing direct) "Al" wrote in message ... Ok that makes sense but what purpose do the "--" operators perform? "Biff" wrote: 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 -----Original Message----- 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" wrote: Hi! Try this: =SUMPRODUCT(--(Sheet1!A1:A100="name"),--(Sheet1! B1:B100="product"),--(Sheet1!C1:C100="score")) Biff -----Original Message----- 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? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"countif" from multiple worksheets within workbook | Excel Discussion (Misc queries) | |||
Subtotal And Countif | Excel Discussion (Misc queries) | |||
countif, again | Excel Worksheet Functions | |||
combining countif formulas | Excel Worksheet Functions | |||
COUNTIF Question | Excel Worksheet Functions |