Please help!!! - Counting if 2 conditions are met in 2 different ranges....

[JRM]

This has really stumped me so I hope one of you experts can help on
this.

I have 2 different columns, one for a position (such as Senior,
Junior) and one for Approved or not (Yes or No values). I want to
COUNT how many times column A is "Senior" and column B is "Yes".

How do I do this???? I have tried many different conditional
statements all to no success. Any help will be greatly appreciated.

The table is pretty much as below:

Position Approved
Super Yes
Super No
Senior No
Senior Yes
Senior Yes
Senior Yes
Senior No
Assoc Yes
Senior No
Assoc Yes
Senior Yes
Super No
Super Yes
Assoc Yes
Senior Yes
Assoc Yes
Senior No
Assoc Yes
Assoc Yes
Assoc No
Senior Yes

So you would assume that the totals of:

Super AND Yes = 2
Senior AND Yes = 6
Assoc AND Yes = 6

I just can't get it to match so far.

Please post a follow up reply here!
Thanks
John

 

[Peo Sjoblom]

Try

=SUMPRODUCT(--(A2:A300="Senior"),--(B2:B300="Yes"))


or better


=SUMPRODUCT(--(A2:A300=D1),--(B2:B300=E1))

where D and E1 holds the conditions, what's better with that is that you
don't have to
edit the formula itself but only change the contents of those 2 cells

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Leo Heuser]

John

One way:

=SUMPRODUCT((A2:A100="Senior")*(B2:B100="Yes"))

 

[Trevor Shuttleworth]

John

one to start you off:

=SUMPRODUCT((A1:A22="super")*(B1:B22="yes"))

Regards

Trevor

 

[Tom Ogilvy]

or even
=SUMPRODUCT(-(A2:A300="Senior"),-(B2:B300="Yes"))

 

[Ludavhen]

You might as well use a pivot report if you want info from adjacent columns
as well.

 

[Harlan Grove]

or even
=SUMPRODUCT(-(A2:A300="Senior"),-(B2:B300="Yes"))

....

Yes, this works mahvelously when there are an even number of conditions, but
not so well when there are an odd number. If one doesn't mind counting
conditions (not all that difficult), then this simplification could be used,
but it's not as safe using -- generally.

 

[JRM]

To all that replied a great big THANK YOU!

Cheers
John