Comparing Two Columns of Data

[switty]

I need to compare two columns of data. The first column will have names, A, B, C, D,.... and the second column will have yes, no, and blanks. I need to be able for a given value in the first column to be able to count the number of cells in the second column that are in the same row where the given value in the first column is that have either "yes" or "no" in one formula, then in a seperate formula count the blanks.

For Example<pre>
Column 1 Column 2
Bill Yes
Steve No
Bill Yes
Steve
Jim Yes
Bob No
Bill No
Bill
Steve No</pre>

Yes/No output for Bill=3
Blank output for Bill=1
Yes/No output for Steve=2
Blank output for Steve=1
....


It seems simple, but I cannot get past looking for "Bill" and if "Yes" in the second column then count. I can count one IF statement, but am not able to count a second IF statement.

Any help or hints appreciated!

Thanks,

Brian

EDIT: tags added
This message was edited by PaddyD on 2002-12-16 16:57

 

[PaddyD]

in general, multi condition counts can be done with sumproduct():

=sumproduct((range1=condition1)*(range2=condition2))

so:

=sumproduct((range1="Bill")*(range2={"Yes","No}))

and

=sumproduct((range1="Bill")*(len(range2)=0))

paddy

 

[Aladin Akyurek]

Book9
	A	B	C	D	E	F
1	name	Y/N		name	Y/N	Blank
2	Bill	Yes		Bill	3	1
3	Steve	No		Steve	2	1
4	Bill	Yes		Jim	1	0
5	Steve			Bob	1	0
6	Jim	Yes
7	Bob	No
8	Bill	No
9	Bill
10	Steve	No
11
Sheet1

The formulas are...

E2:

=SUMPRODUCT(($A$2:$A$10=D2)*($B$2:$B$10={"Yes","No"}))

F2:

=SUMPRODUCT(($A$2:$A$10=D2)*(LEN($B$2:$B$10)=0))

 

[Mark W.]

The PivotTable approach...

Book2
	A	B	C	D	E	F	G	H	I
1	Name	Yes/No		CountofName	Yes/No2
2	Bill	Yes		Name	Yes/No	(blank)	GrandTotal
3	Steve	No		Bill	3	1	4
4	Bill	Yes		Bob	1		1
5	Steve			Jim	1		1
6	Jim	Yes		Steve	2	1	3
7	Bob	No		GrandTotal	7	2	9
8	Bill	No
9	Bill
10	Steve	No
11
12
Sheet1

Note: The "Yes" and "No" values have been Grouped into a new field, 'Yes/No2'.

 

[switty]

Thanks Everybody!!!! Worked perfectly!

I have found the new "Bible" for Excel formulas!!!!!

I will try to contribute to the cause if I can!

Brian