Conditional Counting

[izak]

Hello,
I have a spread sheet full of customer data; about 1600 lines worth.
Each line is a separate account. Column L has thier primary phon
number in the form of xxx-xxx-xxxx. Column Q has an account statu
which is 1, 2, 5, 7, 11 or 99.
How can I count the number of accounts that are in a specific area cod
and prefix that have an acount status of 1? And then so on and s
forth?
For instance, I would like to know the sum of all customers/account
that have a phone number begining with 815-947 and are account statu
1.
Likewise I would like to know all customers/accounts that have a phon
number begining with 815-858 and have an account status of 1.
There are 12 different phone prefixes for which we need to tabulat
quantities.
I have tried doing this with an IF statement, but having the last fou
digets of thier phone number as an un-needed variable is causing m
issues. But, I can't discard the last four digets as there would be n
phone number of record if we did so.
Can some one shed some light?
Thanks, Iza

 

[Bob Phillips]

Hi Izak,

Try this

=SUMPRODUCT(--(LEFT(L1:L1600,7)="815-947"),--(Q1:Q1600=1))
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[izak]

Bob,
I appreciate your response, but that is not doing it. That is givin
me a "1" as the result, but there are 100's with that phone prefix

 

[Frank Kabel]

Hi
are you sure that your columns contain exactly this information and you
don't have leading/trainling spaces=?

 

[izak]

Frank,
I just double checked about a dozen cells; there are not any spaces.
Iza

 

[izak]

This works:
=SUMPRODUCT(--(LEFT($L$5:$L$1804,7)="815-947"),--($Q$5:$Q$1804="1"))
I added the quotes around the 1; home free!
Thank you Bob.

One additional question. What does the "--" denote? Is that like a
"and if"

 

[Frank Kabel]

Hi
if you like email me a smaple of your file

 

[Bob Phillips]

Izak,

It is all explained at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--

HTH

RP
(remove nothere from the email address if mailing direct)