Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf for two columns
I have two columns populated with different values.
I would like to count all the cells pair having specific values, i.e. like countif function, but for both cells. For now I am doing this concatenating both columns and after that performing CountIf over resulting column. Another way is to write a piece of code looping and counting, but it seems too much for such a simple thing. Is there any other way of doing that? Without intermediate steps, only using one formula? Thanks, Yakimo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf for two columns
One way:
Assume your columns are A & B. Further assume your criteria are in D1 (for column A) and E1 (for column B): =SUMPRODUCT(--(A1:A1000=D1),--(B1:B1000=E1)) In article , "Yakimoto" wrote: I have two columns populated with different values. I would like to count all the cells pair having specific values, i.e. like countif function, but for both cells. For now I am doing this concatenating both columns and after that performing CountIf over resulting column. Another way is to write a piece of code looping and counting, but it seems too much for such a simple thing. Is there any other way of doing that? Without intermediate steps, only using one formula? Thanks, Yakimo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf for two columns
Thanks J.E., this is exactly what I needed.
Could you tell me what are these two minuses for in the formula (--(A1:A1000=D1) ? "J.E. McGimpsey" wrote in message ... One way: Assume your columns are A & B. Further assume your criteria are in D1 (for column A) and E1 (for column B): =SUMPRODUCT(--(A1:A1000=D1),--(B1:B1000=E1)) In article , "Yakimoto" wrote: I have two columns populated with different values. I would like to count all the cells pair having specific values, i.e. like countif function, but for both cells. For now I am doing this concatenating both columns and after that performing CountIf over resulting column. Another way is to write a piece of code looping and counting, but it seems too much for such a simple thing. Is there any other way of doing that? Without intermediate steps, only using one formula? Thanks, Yakimo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf for two columns
SUMPRODUCT returns an array of True/False values. The -- is to coerce these
into 0/1 values that can be added up. Could I suggest that you look at this previous NG posting where Ken Wright gives a pretty thorough explanation http://tinyurl.com/v85r -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Yakimoto" wrote in message ... Thanks J.E., this is exactly what I needed. Could you tell me what are these two minuses for in the formula (--(A1:A1000=D1) ? "J.E. McGimpsey" wrote in message ... One way: Assume your columns are A & B. Further assume your criteria are in D1 (for column A) and E1 (for column B): =SUMPRODUCT(--(A1:A1000=D1),--(B1:B1000=E1)) In article , "Yakimoto" wrote: I have two columns populated with different values. I would like to count all the cells pair having specific values, i.e. like countif function, but for both cells. For now I am doing this concatenating both columns and after that performing CountIf over resulting column. Another way is to write a piece of code looping and counting, but it seems too much for such a simple thing. Is there any other way of doing that? Without intermediate steps, only using one formula? Thanks, Yakimo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
CountIf for two columns
SUMPRODUCT expects numeric values. (A1:A1000=D1) returns an array of
boolean (TRUE/FALSE) values, which would be ignored. Boolean values are coerced by XL to 1 (TRUE) and 0 (FALSE) in math formulas, so putting a "unary minus operator" (negative sign) in front of the quantity converts the array to numbers (i.e, -1 for TRUE and 0 for FALSE). The second unary minus converts the -1s to positive values. These arrays are then multiplies and summed by SUMPRODUCT(). In article , "Yakimoto" wrote: Thanks J.E., this is exactly what I needed. Could you tell me what are these two minuses for in the formula (--(A1:A1000=D1) ? "J.E. McGimpsey" wrote in message ... One way: Assume your columns are A & B. Further assume your criteria are in D1 (for column A) and E1 (for column B): =SUMPRODUCT(--(A1:A1000=D1),--(B1:B1000=E1)) In article , "Yakimoto" wrote: I have two columns populated with different values. I would like to count all the cells pair having specific values, i.e. like countif function, but for both cells. For now I am doing this concatenating both columns and after that performing CountIf over resulting column. Another way is to write a piece of code looping and counting, but it seems too much for such a simple thing. Is there any other way of doing that? Without intermediate steps, only using one formula? Thanks, Yakimo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF with 2 columns | Excel Worksheet Functions | |||
COUNTIF for 2 columns | Excel Worksheet Functions | |||
COUNTIF - across 2 columns | Excel Worksheet Functions | |||
Countif for 2 columns | Excel Worksheet Functions | |||
countif in 2 columns | Excel Discussion (Misc queries) |