Conditional Occurrance Count

[Ian Westwell]

Does anyone know how to count from a table, how many times
a cell in column A = "X" when a cell in Column B = "Y".
Countif allows you to do it for one column but will not
allow you to apply a condition. Unless anyone knows
different.

 

[Bob Phillips]

Ian,

=SUMPRODUCT((A1:A100="X")*(B1:B100="Y"))

I am afraid you cannot use a whole column as you can with COUNTIF, you must
specify the limits of the range.

--

HTH

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

 

[Kevin Stecyk]

Hi Ian,

Say you have A1:A100 and B1:B100

=SUMPRODUCT((A1:A100="X")*(B1:B100="Y"))

Here is a good link for you:

http://www.microsoft.com/office/previous/xp/columns/column10.asp

It is by John Walkenbach.

Hope this helps.

Regards,
Kevin

 

[Eric]

Here's one way:

=SUMPRODUCT((A1:A10="X")*(B1:B10="Y"))

Eric

 

[Ian Westwell]

3 replies in as many hours and all with the correct
answer....thanks