Multiple criteria count

[Siny-j]

Hi ppl,

Well what i want the formula to calculate is, count the rows whic
contains lets say "Companyname" in column B and "yes" in column I.
But with the formula i use now it doesn't include the rows wit
"Companyname USA" so how can i achieve this ??

And this is what i got so far:
{=SUM(IF('2003'!B1:B1500="companyname";IF('2003'!I1:I1500="yes";1;0)))}


Thnx in Advance
Siny-

 

[Bob Phillips]

Siny,

Simpler non-array formula

=SUMPRODUCT(('2003'!B1:B1500="companyname")*('2003'!I1:I1500="yes"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi ppl,

Well what i want the formula to calculate is, count the rows which
contains lets say "Companyname" in column B and "yes" in column I.
But with the formula i use now it doesn't include the rows with
"Companyname USA" so how can i achieve this ??

And this is what i got so far:
{=SUM(IF('2003'!B1:B1500="companyname";IF('2003'!I1:I1500="yes";1;0)))}


Thnx in Advance
Siny-j


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements

 

[J.E. McGimpsey]

One way:


=SUMPRODUCT(--(LEFT(B1:B1500,11)="companyname"),--(I1:I1500="Yes"))

 

[Siny-j]

Bob,

Well with the 1 u just gave me i get the same result as with th
formula i posted.
And the formula i posted wasn't wrong in itself but what i want it d
is also count i.e. "Companyname USA" because now it only count
"Companyname".
So i tried it by adding * like this >>
=SUM(IF('2003'!B2:B1500="companyname*";IF('2003'!I2:I1500="Yes";1;0))


but then it doesn't counts any row anymore with the companyname in it

 

[Bob Phillips]

Siny,

How about this

=SUMPRODUCT((LEFT(B1:B1500,11)="Companyname")*(I1:I1500="yes"))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,

Well with the 1 u just gave me i get the same result as with the
formula i posted.
And the formula i posted wasn't wrong in itself but what i want it do
is also count i.e. "Companyname USA" because now it only counts
"Companyname".
So i tried it by adding * like this >>
=SUM(IF('2003'!B2:B1500="companyname*";IF('2003'!I2:I1500="Yes";1;0)))


but then it doesn't counts any row anymore with the companyname in it.


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to

creating financial statements