Doing a count of a value in another collumn

[ewb1981]

In collumn P I have several different values. In the next few collumns i either have yes or no to the the various values. Ive already created formulas to count the yes or no values. However what I'm really looking for is a way to count the occurances of no blank spaces for the critera in P. I want to look at collumn P but count the occurances of istext for Q. Not sure if im really being clear but my excel workbook looks a bit like the following

P Q R
Customer YES YES
Prospect NO
Partner YES
Customer

I would like to be able to find a formula that would tell me that there is 1 customer 1 prospect and 1 partner in Q for this example of the workbook.

 

[Aladin Akyurek]

On 2002-08-29 06:24, ewb1981 wrote:
In collumn P I have several different values. In the next few collumns i either have yes or no to the the various values. Ive already created formulas to count the yes or no values. However what I'm really looking for is a way to count the occurances of no blank spaces for the critera in P. I want to look at collumn P but count the occurances of istext for Q. Not sure if im really being clear but my excel workbook looks a bit like the following

P Q R
Customer YES YES
Prospect NO
Partner YES
Customer

I would like to be able to find a formula that would tell me that there is 1 customer 1 prospect and 1 partner in Q for this example of the workbook.

Let P1:R5 house the sample you provided:

{"Field1","Field2","Field3";
"Customer","YES","YES";
"Prospect","NO","";
"Partner","YES","";
"Customer","",""}

Create a unique list of items from P1:P5 in M from M1 on. You should get:

{"Field1";"Customer";"Prospect";"Partner"}

in M1:M4.

In N2 enter & Copy down:

=SUMPRODUCT(($P$2:$P$5=M2)*(LEN($Q$2:$Q$5)>0))

In O2 enter & copy down:

=SUMPRODUCT(($P$2:$P$5=M2)*(LEN($R$2:$R$5)>0))

See the figure...

aaMultCondCounting ewb1981.xls
	M	N	O	P	Q	R
1	Field1			Field1	Field2	Field3
2	Customer	1	1	Customer	YES	YES
3	Prospect	1	0	Prospect	NO
4	Partner	1	0	Partner	YES
5				Customer
Sheet1