Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Queries?
I can't remember how to do a multiple criteria "countif" query. I need to
count the occurrances of times when a specific entry in column B is followed by a different specific entry in the same row in column C. I can't recall what you call this (in order to use Excel's Help), but I think you had to simultaneously press Alt-Shift-Enter to make it work... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Queries?
Array formula is the term I believe you are looking for and they require
Cntrl+Shift+Enter (CSE). =SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe")) would count the number of times 123 appears in B1:B10 where the same row in column C has the name "Joe". In this case, CSE is not required as Sumproduct accepts array arguments. More on Sumproduct, Array Formulae, and multiple condition tests: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.cpearson.com/excel/array.htm "Russell Seguin" wrote: I can't remember how to do a multiple criteria "countif" query. I need to count the occurrances of times when a specific entry in column B is followed by a different specific entry in the same row in column C. I can't recall what you call this (in order to use Excel's Help), but I think you had to simultaneously press Alt-Shift-Enter to make it work... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Queries?
I tried your formula before and it worked but I don't understand why
sumproduct works to count up stuff rather than multiply it. Also, the two columns I'm checking are both numerical as opposed to your example of 1 column being numeric and 1 column being text. I had tried everything to get an array function to work, but couldn't so was VERY glad to find your response...but can you explain why it works and why it won't work without the --? Thanks!!!! "JMB" wrote: Array formula is the term I believe you are looking for and they require Cntrl+Shift+Enter (CSE). =SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe")) would count the number of times 123 appears in B1:B10 where the same row in column C has the name "Joe". In this case, CSE is not required as Sumproduct accepts array arguments. More on Sumproduct, Array Formulae, and multiple condition tests: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.cpearson.com/excel/array.htm "Russell Seguin" wrote: I can't remember how to do a multiple criteria "countif" query. I need to count the occurrances of times when a specific entry in column B is followed by a different specific entry in the same row in column C. I can't recall what you call this (in order to use Excel's Help), but I think you had to simultaneously press Alt-Shift-Enter to make it work... |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Queries?
Bob Phillips has a detailed explanation on this usage of Sumproduct, which I
would recommend reviewing. http://www.xldynamic.com/source/xld.SUMPRODUCT.html Using my example for counting: =SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe")) If you have: B C 123 Bob 456 Joe 123 Joe 34 Frank 0 Estelle 123 123 Joe 23 Joe Harry 19 Debbie It will test the first column for 123 and the second column for "Joe". This conditional comparison will return arrays of TRUE or FALSE TRUE FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE FALSE TRUE FALSE TRUE TRUE FALSE TRUE FALSE FALSE FALSE FALSE Excel stores TRUE as 1 and FALSE as 0 (TRUE/FALSE is just what is displayed on screen for our benefit). When you perform a math operation on boolean (ie TRUE/FALSE) values, they are coerced to their underlying values (1 or 0). One way of coercing these values is w/ the double unary operator (double negative). So the arrays are coerced to: 1 0 0 1 1 1 0 0 0 0 1 0 1 1 0 1 0 0 0 0 Sumproduct then multiplies the arrays: 0 0 1 0 0 0 1 0 0 0 and adds up these results to arrive at 2. "MC" wrote: I tried your formula before and it worked but I don't understand why sumproduct works to count up stuff rather than multiply it. Also, the two columns I'm checking are both numerical as opposed to your example of 1 column being numeric and 1 column being text. I had tried everything to get an array function to work, but couldn't so was VERY glad to find your response...but can you explain why it works and why it won't work without the --? Thanks!!!! "JMB" wrote: Array formula is the term I believe you are looking for and they require Cntrl+Shift+Enter (CSE). =SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe")) would count the number of times 123 appears in B1:B10 where the same row in column C has the name "Joe". In this case, CSE is not required as Sumproduct accepts array arguments. More on Sumproduct, Array Formulae, and multiple condition tests: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.cpearson.com/excel/array.htm "Russell Seguin" wrote: I can't remember how to do a multiple criteria "countif" query. I need to count the occurrances of times when a specific entry in column B is followed by a different specific entry in the same row in column C. I can't recall what you call this (in order to use Excel's Help), but I think you had to simultaneously press Alt-Shift-Enter to make it work... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Queries?
What if my first criteria is matching the value in one column to an array and
the second criteria is matching to a set string, say "Yes")? Here's my data set: A B XYZ Yes XYZ No ABC Yes ABC No LMN No LMN Yes Array named "ID" XYZ LMN Now, my formula needs to check if the value in column A is in the array "ID" AND Column B = Yes, then Add 1 (I believe this is a SUMPRODUCT or COUNTIF function or a combination). I got some help and figured out how to use a COUNT(MATCH) combo to check column A to see if it's in the array, but can't figure out how to use it in a multiple condition function. "JMB" wrote: Bob Phillips has a detailed explanation on this usage of Sumproduct, which I would recommend reviewing. http://www.xldynamic.com/source/xld.SUMPRODUCT.html Using my example for counting: =SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe")) If you have: B C 123 Bob 456 Joe 123 Joe 34 Frank 0 Estelle 123 123 Joe 23 Joe Harry 19 Debbie It will test the first column for 123 and the second column for "Joe". This conditional comparison will return arrays of TRUE or FALSE TRUE FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE FALSE TRUE FALSE TRUE TRUE FALSE TRUE FALSE FALSE FALSE FALSE Excel stores TRUE as 1 and FALSE as 0 (TRUE/FALSE is just what is displayed on screen for our benefit). When you perform a math operation on boolean (ie TRUE/FALSE) values, they are coerced to their underlying values (1 or 0). One way of coercing these values is w/ the double unary operator (double negative). So the arrays are coerced to: 1 0 0 1 1 1 0 0 0 0 1 0 1 1 0 1 0 0 0 0 Sumproduct then multiplies the arrays: 0 0 1 0 0 0 1 0 0 0 and adds up these results to arrive at 2. "MC" wrote: I tried your formula before and it worked but I don't understand why sumproduct works to count up stuff rather than multiply it. Also, the two columns I'm checking are both numerical as opposed to your example of 1 column being numeric and 1 column being text. I had tried everything to get an array function to work, but couldn't so was VERY glad to find your response...but can you explain why it works and why it won't work without the --? Thanks!!!! "JMB" wrote: Array formula is the term I believe you are looking for and they require Cntrl+Shift+Enter (CSE). =SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe")) would count the number of times 123 appears in B1:B10 where the same row in column C has the name "Joe". In this case, CSE is not required as Sumproduct accepts array arguments. More on Sumproduct, Array Formulae, and multiple condition tests: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.cpearson.com/excel/array.htm "Russell Seguin" wrote: I can't remember how to do a multiple criteria "countif" query. I need to count the occurrances of times when a specific entry in column B is followed by a different specific entry in the same row in column C. I can't recall what you call this (in order to use Excel's Help), but I think you had to simultaneously press Alt-Shift-Enter to make it work... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Queries?
see response to your earlier posting
-- Regards Roger Govier "sweens319" wrote in message ... What if my first criteria is matching the value in one column to an array and the second criteria is matching to a set string, say "Yes")? Here's my data set: A B XYZ Yes XYZ No ABC Yes ABC No LMN No LMN Yes Array named "ID" XYZ LMN Now, my formula needs to check if the value in column A is in the array "ID" AND Column B = Yes, then Add 1 (I believe this is a SUMPRODUCT or COUNTIF function or a combination). I got some help and figured out how to use a COUNT(MATCH) combo to check column A to see if it's in the array, but can't figure out how to use it in a multiple condition function. "JMB" wrote: Bob Phillips has a detailed explanation on this usage of Sumproduct, which I would recommend reviewing. http://www.xldynamic.com/source/xld.SUMPRODUCT.html Using my example for counting: =SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe")) If you have: B C 123 Bob 456 Joe 123 Joe 34 Frank 0 Estelle 123 123 Joe 23 Joe Harry 19 Debbie It will test the first column for 123 and the second column for "Joe". This conditional comparison will return arrays of TRUE or FALSE TRUE FALSE FALSE TRUE TRUE TRUE FALSE FALSE FALSE FALSE TRUE FALSE TRUE TRUE FALSE TRUE FALSE FALSE FALSE FALSE Excel stores TRUE as 1 and FALSE as 0 (TRUE/FALSE is just what is displayed on screen for our benefit). When you perform a math operation on boolean (ie TRUE/FALSE) values, they are coerced to their underlying values (1 or 0). One way of coercing these values is w/ the double unary operator (double negative). So the arrays are coerced to: 1 0 0 1 1 1 0 0 0 0 1 0 1 1 0 1 0 0 0 0 Sumproduct then multiplies the arrays: 0 0 1 0 0 0 1 0 0 0 and adds up these results to arrive at 2. "MC" wrote: I tried your formula before and it worked but I don't understand why sumproduct works to count up stuff rather than multiply it. Also, the two columns I'm checking are both numerical as opposed to your example of 1 column being numeric and 1 column being text. I had tried everything to get an array function to work, but couldn't so was VERY glad to find your response...but can you explain why it works and why it won't work without the --? Thanks!!!! "JMB" wrote: Array formula is the term I believe you are looking for and they require Cntrl+Shift+Enter (CSE). =SUMPRODUCT(--(B1:B10=123), --(C1:C10="Joe")) would count the number of times 123 appears in B1:B10 where the same row in column C has the name "Joe". In this case, CSE is not required as Sumproduct accepts array arguments. More on Sumproduct, Array Formulae, and multiple condition tests: http://www.xldynamic.com/source/xld.SUMPRODUCT.html http://www.cpearson.com/excel/array.htm "Russell Seguin" wrote: I can't remember how to do a multiple criteria "countif" query. I need to count the occurrances of times when a specific entry in column B is followed by a different specific entry in the same row in column C. I can't recall what you call this (in order to use Excel's Help), but I think you had to simultaneously press Alt-Shift-Enter to make it work... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
Conditional formula - language needed | Excel Worksheet Functions | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) | |||
Can i carry out more than 3 conditional formating Queries? how? | Excel Discussion (Misc queries) |