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

  • Thread starter Thread starter JRM
  • Start date Start date
J

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
 
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)
 
John

one to start you off:

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

Regards

Trevor
 
Tom Ogilvy said:
or even
=SUMPRODUCT(-(A2:A300="Senior"),-(B2:B300="Yes"))
Click to expand...
....

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.
 
Back
Top