Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Lookup using two values - like an SQL in the same workbook
I have a table of data. Elsewhere in the workbook, I want to include a
formula to count all the rows in that table that have a value of "A" in the first column and a "B" in the second column. Kind of like select count(*) from table where column1="A" and column2="B" Is this possible? |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Lookup using two values - like an SQL in the same workbook
Hi Hall,
Hall wrote: I have a table of data. Elsewhere in the workbook, I want to include a formula to count all the rows in that table that have a value of "A" in the first column and a "B" in the second column. Kind of like select count(*) from table where column1="A" and column2="B" Is this possible? You can do this pretty easily with an Array Formula: =SUM(($A$1:$A$9="A")*($B$1:$B$9="B")) This assumes your list is in A1:B9. The above formula, when array-entered (***you must use Ctrl+Shift+Enter instead of just Enter to create an array formula***), will give you the count you're looking for. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
#3
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Lookup using two values - like an SQL in the same workbook
Hall wrote:
I have a table of data. Elsewhere in the workbook, I want to include a formula to count all the rows in that table that have a value of "A" in the first column and a "B" in the second column. Kind of like With the As in A1:A10 and the Bs in B1:B10 use this formula: =SUMPRODUCT((A1:A10="a")*(B1:B10="b")) You can also use =SUM((A1:A10="a")*(B1:B10="b")) as a matrixformula (end it with ctrl+shift+enter) if you like. /Sune |
#4
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Lookup using two values - like an SQL in the same workbook
"Hall" wrote in message ... I have a table of data. Elsewhere in the workbook, I want to include a formula to count all the rows in that table that have a value of "A" in the first column and a "B" in the second column. Kind of like select count(*) from table where column1="A" and column2="B" Is this possible? Hi Have a look on this link, which explains a easy way to do your task: http://www.xldynamic.com/source/xld.SUMPRODUCT.html |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Lookup using two values - like an SQL in the same workbook
One alternative is to use a helper column and CONCATENATE columns A and B
into it and then use the regular formula =COUNTIF(C:C,"AB") Vaya con Dios, Chuck, CABGx3 "Hall" wrote in message ... I have a table of data. Elsewhere in the workbook, I want to include a formula to count all the rows in that table that have a value of "A" in the first column and a "B" in the second column. Kind of like select count(*) from table where column1="A" and column2="B" Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i wish to lookup values in column A, & add adjacent values in colu | Excel Discussion (Misc queries) | |||
How do I call number values form one workbook to another workbook | Excel Worksheet Functions | |||
How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |