How do I count items based on multiple criteria from a different worksheet?

[Mctabish]

I have tried {=SUM(('10-16-2005'!F:F="Brentwood") *
('10-16-2005'!B:B="ACTV"))} (entered with F2, then cntrl/shift /return, but
it is not returning what It should. What I would REALLY like to do, is if
the first letter of column B (STATUS) starts with a "A", "B" or "N" .AND.
Column F (City) = "Brentwood" .AND. Column J (BT) = "DE". All of these are
on Worksheet 10-16-05 (or preferrably, what ever the column header is on
SHEET1, row1, column())


Thanks!
Mc

 

[Bob Phillips]

Here is a formula

=SUMPRODUCT((ISNUMBER(FIND({"A";"B";"N"},LEFT('10-06-2005'!B2:B20,1)))*('10-
06-2005'!F2:F20="Brentwood")*('10-06-2005'!J2:J20="DE")))

Note that SUMPRODUCT cannot work on a whole column, you have to define the
number of rows.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Mctabish]

I see the logic (for the most part), but I am not familiar with the CURLY
BRACES in an FIND statement. I guess it is similair to find X OR Y OR Z?
The results don't return what they should (always "0") I have tried to break
the formula down into segments, and it seems that the FIND portion is what
is not working for me....
Any suggestions?
TIA,
MC


Here is a small sample of the data in CSV
MLS
No,Status,DOM,Address,Unit,City,Area,LP,SP,BT,SqFt,BR,Bth,PB,Gar?,Gar#,YrBlt,TB
Map,Acres,Lot SqFt,HOA $,Freq
40116903,ACTV ,8,,,LIVERMORE,4000 ,,,TH
,982,2,1,1,N,0,1983,695F6,6.48,282268,211,M
40116904,ACTV ,6,,,CASTRO VALLEY,3300 ,,,DE
,1650,3,2,0,Y,2,1986,692A4,0.13,5400,20,M
40116905,ACTV ,6,,,SAN RAMON,4400 ,,,DE
,1465,3,2,0,Y,2,1971,673G6,0.36,15750,,
40116918,ACTV ,6,,,SAN LEANDRO,3100 ,,,CO
,1060,3,2,0,Y,1,1979,690H5,0,0,227,M
40116923,ACTV ,6,,,BRENTWOOD,6400 ,,,DE
,1822,4,2,1,Y,2,1993,616E1,0.11,5000,,
40116924,ACTV ,6,,,CASTRO VALLEY,3300 ,,,DE
,1645,3,2,1,Y,2,1959,692A5,0.11,5002,,
40116928,ACTV ,6,,,CASTRO VALLEY,3300 ,,,DE
,860,2,1,0,Y,1,1941,691G5,0.11,4860,,
40116931,PEND ,3,,,DANVILLE,4500 ,,,DE
,4110,4,3,0,Y,3,1989,632J7,1,43560,,
40116938,ACTV ,6,,,BRENTWOOD,6400 ,,,DE
,1745,4,2,0,Y,2,1997,616E3,0,6600,,
40116942,ACTV ,6,,,SAN LEANDRO,3100 ,,,DE
,2207,4,2,1,Y,2,2002,670j7,0.08,3689,82,M
40116948,ACTV ,6,,,LIVERMORE,4000 ,,,DE
,1900,4,2,0,Y,2,1958,715J1,0.14,6044,,
40116959,ACTV ,6,,,BRENTWOOD,6400 ,,,DE
,2574,5,3,0,Y,3,1998,616J2,0.16,6775,,
40116960,ACTV ,6,,,PLEASANTON,3900 ,,,TH
,1120,2,1,1,N,1,1979,694f5,0.02,787,175,M
40116966,ACTV ,6,,,BRENTWOOD,6400 ,,,DE
,2449,6,3,0,N,3,1995,616G3,0.15,6500,,
40116973,ACTV ,6,,,LIVERMORE,4000 ,,,DE
,1144,3,2,0,Y,2,1965,695E6,0.14,6200,,
40116991,ACTV ,6,,,PLEASANTON,3900 ,,,DE
,1931,4,2,0,Y,2,1969,714D1,0.16,7154,,
40116993,ACTV ,6,,,SAN LORENZO,3200 ,,,DE
,1350,3,2,1,Y,2,2005,691F6,0,3328,,
40116996,ACTV ,6,,,PLEASANTON,3900 ,,,DE
,1872,3,2,1,Y,2,1971,694F6,0.14,6268,168,A
40116999,AC ,5,,,LIVERMORE,4000 ,,,DE
,2000,4,3,0,Y,2,1998,696E2,0.14,6000,,
40117001,ACTV ,5,,,PLEASANTON,3900 ,,,DE
,4791,5,4,1,Y,3,2004,714B5,0.31,13455,,
40117004,NEW ,5,,,SAN RAMON,4400 ,,,DE
,2365,4,2,0,Y,3,1995,673J6,0.2,8770,,
40117012,NEW ,8,,,CASTRO VALLEY,3300 ,,,DE
,2026,4,2,1,N,2,1987,691J4,0.12,5100,,
40117021,NEW ,5,,,SAN LEANDRO,3100 ,,,DE
,1008,3,1,0,Y,0,1942,690J2,0.11,5000,,
40117025,NEW ,5,,204E,SAN LEANDRO,3100 ,,,CO
,1022,2,1,1,Y,1,1981,691G4,0,0,310,M
40117028,NEW ,5,,,DANVILLE,4500 ,,,DE
,2500,4,3,,Y,3,1971,653D6,0.21,9086,761,A
40117030,NEW ,5,,,LIVERMORE,4000 ,,,DE
,2246,4,2,1,Y,2,1993,696b6,0.18,7974,,
40117031,NEW ,6,,,CASTRO VALLEY,3300 ,,,DE
,2222,4,2,1,Y,2,1950,733F5,2.72,118483,,