Lookup using two values - like an SQL in the same workbook

  • Thread starter Thread starter Hall
  • Start date Start date
H

Hall

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 Hall,
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?
Click to expand...

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]
 
Hall said:
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
Click to expand...

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
 
Hall said:
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?
Click to expand...

Hi

Have a look on this link, which explains a easy way to do your task:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
 
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
 
Back
Top