how do I count pairs of cells when each matches a condition?
 

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!

how do I count pairs of cells when each matches a condition?
 

You can use an array formula or just a helper column:

In an un-used column enter:

=AND(A1="x",B1="y")*1 and copy down. Then just sum the contents of the new
column.
--
Gary's Student


"Richard pile" wrote:

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!

how do I count pairs of cells when each matches a condition?
 

For a single formula approach, try something like this:

For numbers in Col_A and text in Col_B

This formula counts to number of times Col_A contains the number 1 when
Col_B contains the letter "d":
C1: =SUMPRODUCT((A1:A10=1)*(B1:B10="d"))

Adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Richard pile" wrote:

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!

how do I count pairs of cells when each matches a condition?
 

=sumproduct(--(a1:a10="x"),--(b1:b10=33))

Change the range--but don't use the whole column.

Richard pile wrote:

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!

--

Dave Peterson

how do I count pairs of cells when each matches a condition?
 

Try this:

if X is in C1 and Y is in D1 then
=SUMPRODUCT((A1:A10=C1)*(B1:B10=D1))
or
=SUMPRODUCT((A1:A10=x)*(B1:B10="y"))
where x = nemerical value (without quotes) and y = text (inside quotes) or
vise versa

HTH
Jean-Guy

"Richard pile" wrote:

I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B
simultaneously has a value of "y" (actually one would be a numerical value
and one text). I am getting confused looking at the help section for array
formulae.
Help?!