Look-up Formula

[PeterD]

I have been racking my brain to try to figure this formula out, it probably is very simple.

A1:A10 will have values of 1-4.
B1:B10 will have values of 1-3

I require a formula that will tell me in A1:A10 there are "3" "1's" and in the B Column the number of corresponding numbers for the 1's. So, the 3 - 1's have a 2-1's corresponding and 1-2 corresponding in the B column.

Hopefully this makes sense and I appreciate your help.

Peter

[Aladin Akyurek]

On 2002-05-13 22:07, PeterD wrote:
I have been racking my brain to try to figure this formula out, it probably is very simple.

A1:A10 will have values of 1-4.
B1:B10 will have values of 1-3

I require a formula that will tell me in A1:A10 there are "3" "1's" and in the B Column the number of corresponding numbers for the 1's. So, the 3 - 1's have a 2-1's corresponding and 1-2 corresponding in the B column.

Hopefully this makes sense and I appreciate your help.

Peter

The figure below shows a formula-based approach.

Microsoft Excel - aaCounting PeterD.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
I10		=
*	A	B	C	D	E	F	G	H
1	1	1	*	*	Total Count	1	2	3
2	2	1	*	1	:alert('=COUNTIF($A$1:$A$10,D2)')>3	:alert('=SUMPRODUCT(($A$1:$A$10=$D2)*($B$1:$B$10=F$1))')>2	:alert('=SUMPRODUCT(($A$1:$A$10=$D2)*($B$1:$B$10=G$1))')>1	:alert('=SUMPRODUCT(($A$1:$A$10=$D2)*($B$1:$B$10=H$1))')>0
3	3	2	*	2	:alert('=COUNTIF($A$1:$A$10,D3)')>4	:alert('=SUMPRODUCT(($A$1:$A$10=$D3)*($B$1:$B$10=F$1))')>1	:alert('=SUMPRODUCT(($A$1:$A$10=$D3)*($B$1:$B$10=G$1))')>1	:alert('=SUMPRODUCT(($A$1:$A$10=$D3)*($B$1:$B$10=H$1))')>2
4	2	3	*	3	:alert('=COUNTIF($A$1:$A$10,D4)')>2	:alert('=SUMPRODUCT(($A$1:$A$10=$D4)*($B$1:$B$10=F$1))')>1	:alert('=SUMPRODUCT(($A$1:$A$10=$D4)*($B$1:$B$10=G$1))')>1	:alert('=SUMPRODUCT(($A$1:$A$10=$D4)*($B$1:$B$10=H$1))')>0
5	1	1	*	4	:alert('=COUNTIF($A$1:$A$10,D5)')>1	:alert('=SUMPRODUCT(($A$1:$A$10=$D5)*($B$1:$B$10=F$1))')>0	:alert('=SUMPRODUCT(($A$1:$A$10=$D5)*($B$1:$B$10=G$1))')>1	:alert('=SUMPRODUCT(($A$1:$A$10=$D5)*($B$1:$B$10=H$1))')>0
6	2	2	*	*	*	*	*	*
7	2	3	*	*	*	*	*	*
8	4	2	*	*	*	*	*	*
9	3	1	*	*	*	*	*	*
10	1	2	*	*	*	*	*	*
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.22]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


E2 houses the formula

=COUNTIF($A$2:$A$11,D2)

which is copied down for distinct A-items.

F2 houses the formula

=SUMPRODUCT(($A$2:$A$11=$D2)*($B$2:$B$11=F$1))

that counts the co-occurrence of A-items and B-items.

This can also be done with PivotTables. Give that too a try.

Aladin

[PeterD]

Thank you, I can sleep again in peace! It works great. I actually wanted to replace a pivot table with a formula and now it is done.....Peter

[ This Message was edited by: PeterD on 2002-05-15 21:30 ]