Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif in 2 columns
Using XL2002 for Window, I need to count the number of items that
match criteria in both of 2 columns. Column A contains a list of numbers that can range from 1-12, corresponding to the month, and column D has an individuals initials. I want to be able to determine, for example, how many entries would match "1" in column A and "JC" in column D. Is there a function that will do that? Thanks, Tonso |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif in 2 columns
Use SUMPRODUCT.
=SUMPRODUCT(--(A1:A100=1),--(B1:B100="JC")) HTH, Elkar "Tonso" wrote: Using XL2002 for Window, I need to count the number of items that match criteria in both of 2 columns. Column A contains a list of numbers that can range from 1-12, corresponding to the month, and column D has an individuals initials. I want to be able to determine, for example, how many entries would match "1" in column A and "JC" in column D. Is there a function that will do that? Thanks, Tonso |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif in 2 columns
On Oct 22, 3:39 pm, Elkar wrote:
Use SUMPRODUCT. =SUMPRODUCT(--(A1:A100=1),--(B1:B100="JC")) HTH, Elkar "Tonso" wrote: Using XL2002 for Window, I need to count the number of items that match criteria in both of 2 columns. Column A contains a list of numbers that can range from 1-12, corresponding to the month, and column D has an individuals initials. I want to be able to determine, for example, how many entries would match "1" in column A and "JC" in column D. Is there a function that will do that? Thanks, Tonso- Hide quoted text - - Show quoted text - Thanks so much Elkar! Did just what i wanted it to do. My only question is...how? For example, what does "--" signify? Thanks again! Tonso |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif in 2 columns
SUMPRODUCT works by multiplying each of the arguments, then adding the
results for each value in the range. It only works on numbers, however. Since A1:A100=1 returns either TRUE or FALSE, we need to convert this to a number so SUMPRODUCT can use it. The -- effectively multiplies the TRUE (1) or FALSE (0) by -1 one twice. Thus resulting in 1 or 0. (This may seem redundant, but while Excel recognizes TRUE as 1 and FALSE as 0, SUMPRODUCT doesn't) So, let's use the following data set as an example: A B 1 1 AJ 2 1 JC 3 2 JC Using the SUMPRODUCT formula: A1 is TRUE and B1 is FALSE, therefore 1x0=0. A2 is TRUE and B1 is TRUE, therefore 1x1=1. A3 is FALSE and B1 is TRUE, therefore 0x1=0. Then, add the results together, 0+1+0=1. So, one data set meets both criteria. HTH, Elkar "Tonso" wrote: On Oct 22, 3:39 pm, Elkar wrote: Use SUMPRODUCT. =SUMPRODUCT(--(A1:A100=1),--(B1:B100="JC")) HTH, Elkar "Tonso" wrote: Using XL2002 for Window, I need to count the number of items that match criteria in both of 2 columns. Column A contains a list of numbers that can range from 1-12, corresponding to the month, and column D has an individuals initials. I want to be able to determine, for example, how many entries would match "1" in column A and "JC" in column D. Is there a function that will do that? Thanks, Tonso- Hide quoted text - - Show quoted text - Thanks so much Elkar! Did just what i wanted it to do. My only question is...how? For example, what does "--" signify? Thanks again! Tonso |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif in 2 columns
On Oct 22, 5:12 pm, Elkar wrote:
SUMPRODUCT works by multiplying each of the arguments, then adding the results for each value in the range. It only works on numbers, however. Since A1:A100=1 returns either TRUE or FALSE, we need to convert this to a number so SUMPRODUCT can use it. The -- effectively multiplies the TRUE (1) or FALSE (0) by -1 one twice. Thus resulting in 1 or 0. (This may seem redundant, but while Excel recognizes TRUE as 1 and FALSE as 0, SUMPRODUCT doesn't) So, let's use the following data set as an example: A B 1 1 AJ 2 1 JC 3 2 JC Using the SUMPRODUCT formula: A1 is TRUE and B1 is FALSE, therefore 1x0=0. A2 is TRUE and B1 is TRUE, therefore 1x1=1. A3 is FALSE and B1 is TRUE, therefore 0x1=0. Then, add the results together, 0+1+0=1. So, one data set meets both criteria. HTH, Elkar "Tonso" wrote: On Oct 22, 3:39 pm, Elkar wrote: Use SUMPRODUCT. =SUMPRODUCT(--(A1:A100=1),--(B1:B100="JC")) HTH, Elkar "Tonso" wrote: Using XL2002 for Window, I need to count the number of items that match criteria in both of 2 columns. Column A contains a list of numbers that can range from 1-12, corresponding to the month, and column D has an individuals initials. I want to be able to determine, for example, how many entries would match "1" in column A and "JC" in column D. Is there a function that will do that? Thanks, Tonso- Hide quoted text - - Show quoted text - Thanks so much Elkar! Did just what i wanted it to do. My only question is...how? For example, what does "--" signify? Thanks again! Tonso- Hide quoted text - - Show quoted text - Thanks so much for the explanation. It helped me understand what was happening, and i will save it for future reference, as this seems to be a very useful techique! Tonso |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Countif for multiple columns | Excel Discussion (Misc queries) | |||
Countif Using Multiple Columns | Excel Worksheet Functions | |||
using a countif with multiple columns | Excel Worksheet Functions | |||
Help with countif using two columns | Excel Worksheet Functions | |||
Countif - Two Criteria in two columns are met. | Excel Discussion (Misc queries) |