Countif Statements in Excel 2000

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

Hi I am trying to figure out how to enter a countif
statement with 3 conditions.
For example I have
Column A Column B Column C Column D
Location Title Qualfication Number
Kent Partner MRICS BA HONS 3
Wolves Parnter MRICS 4
Kent Partner MRICS 5
London Senior Partner FRICS 6
Kent Partner FRIC 7

What I need to do is find out how many Partners there are
in Kent that have the MRICS qualification. You will
notice that the qualification column may hold more than 1
qualification, eg: MRICS, BA(HONS), FRICS..etc.

Can anyone please guide me on how to sort this out.

I have managed to get:
=COUNTIF('Staff Schedule'!A:A,"Orpington"*('Staff
Schedule'!B:B,"Partner")*(C:C,"*MRICS*))
But if I try to use concatenation operators..etc , I get
#Ref error message. All help most appreciated. Thanking
you in advance.

Sue
 
Don

I was going to suggest that, but Sue says that the MRICS could be anywhere
in the cell. Now, I hope, you are going to tell both of us how to do it with
this condition!!!

Andy.
 
One way:

=SUMPRODUCT(--(A1:A1000="Kent"), --(B1:B1000="Partner"),
--(C1:C1000="MRICS"))

Each element returns an array of TRUE/FALSE. The "--" coerces the
values into 1/0s since XL treats TRUE as 1 and FALSE as 0 in math
functions. SUMPRODUCT then multiplies the arrays together and sums
the result.
 
Hi Don

Thanks for responding to soon. I have tried your formula
but it gives me the following error:

#NAME?

Any other ideas.

Many thanks
Sue
 
Didn't notice that. This should do it if mrics is to the left
(LEFT(H1:H5,5)="mrics"))
 
Hi

Thanks very much for your response. I have tried your
formula and it is great. It picks up the criteria that
matches exactly: eg: Kent, Partner and MRICS. However,
as I mentioned, the qualifications column contains
numerous qualifications such as MRICS FRICS, BA(HONS)..etc.

I can't get the formula to match the ones that have more
than 1 qualification. I have tried to put in stars either
side of MRICS - eg:

=SUMPRODUCT(--(A1:A1000="Kent"), --(B1:B1000="Partner"),
--(C1:C1000="*MRICS*"))
Click to expand...

Any further assistance would be most welcome.
Regards
Sue
 
Ah, I didn't interpret your stating that the *column* may hold more
than 1 qualification as meaning that individual cells within the
column could hold more than one...

Sorry.

Try:

=SUMPRODUCT(--(A1:A1000="Kent"), --(B1:B1000="Partner"),
--NOT(ISERR(SEARCH("MRICS",C1:C1000))))
 
You started out by mentioning "Kent", but I see "Orpington" in your formula
?!?

Anyway, try this:

=SUMPRODUCT(('Staff Schedule'!A2:A65000="Kent")*('Staff
Schedule'!B2:B65000="Partner")*('Staff Schedule'!C2:C65000="MRICS"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi I am trying to figure out how to enter a countif
statement with 3 conditions.
For example I have
Column A Column B Column C Column D
Location Title Qualfication Number
Kent Partner MRICS BA HONS 3
Wolves Parnter MRICS 4
Kent Partner MRICS 5
London Senior Partner FRICS 6
Kent Partner FRIC 7

What I need to do is find out how many Partners there are
in Kent that have the MRICS qualification. You will
notice that the qualification column may hold more than 1
qualification, eg: MRICS, BA(HONS), FRICS..etc.

Can anyone please guide me on how to sort this out.

I have managed to get:
=COUNTIF('Staff Schedule'!A:A,"Orpington"*('Staff
Schedule'!B:B,"Partner")*(C:C,"*MRICS*))
But if I try to use concatenation operators..etc , I get
#Ref error message. All help most appreciated. Thanking
you in advance.

Sue
 
My OE didn't pick up the entire thread, only the OP.
Needless to say, it doesn't work.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

You started out by mentioning "Kent", but I see "Orpington" in your formula
?!?

Anyway, try this:

=SUMPRODUCT(('Staff Schedule'!A2:A65000="Kent")*('Staff
Schedule'!B2:B65000="Partner")*('Staff Schedule'!C2:C65000="MRICS"))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi I am trying to figure out how to enter a countif
statement with 3 conditions.
For example I have
Column A Column B Column C Column D
Location Title Qualfication Number
Kent Partner MRICS BA HONS 3
Wolves Parnter MRICS 4
Kent Partner MRICS 5
London Senior Partner FRICS 6
Kent Partner FRIC 7

What I need to do is find out how many Partners there are
in Kent that have the MRICS qualification. You will
notice that the qualification column may hold more than 1
qualification, eg: MRICS, BA(HONS), FRICS..etc.

Can anyone please guide me on how to sort this out.

I have managed to get:
=COUNTIF('Staff Schedule'!A:A,"Orpington"*('Staff
Schedule'!B:B,"Partner")*(C:C,"*MRICS*))
But if I try to use concatenation operators..etc , I get
#Ref error message. All help most appreciated. Thanking
you in advance.

Sue
 
Back
Top