Put this in Col D of Sheet1
=SUMPRODUCT(--(Sheet2!A$1:A$100=Sheet1!A1))
to get the count of cells matching A1 in Sheet2!A$1:A$100

You can use the above to get all the numbers you want by changing the range,
lookup value and formula containing the cell appropriately..

If you want to SUM Col B where Col A matches A1 use
=SUMPRODUCT(--(Sheet2!A$1:A$100=Sheet1!A1),(Sheet2!B$1:B$100))
You can add as many conditions as you want...
Put -- in front of all conditions
Put -- in front of the last set (without a condition) if you want the count.
If you leave it out you will get the sum.

-------------------------------------
Pl. click ''''Yes'''' if this was helpful...



"Rob" wrote:

I'm hoping someone would be able to help me solve a quandry of what I am
trying to do. In Excel 2003 I have four worksheets;

Worksheet 1 has unique values in Col "A" that I want to count in Worksheet 1
Col "D" where they match Worksheet 2 Col "A"
Worksheet 2 has values in Col "C" that I want to count in Worksheet 3 Col
"B" and place the total in Worksheet 1 Col "E" as they relate to Worksheet 1
Col "A" Through Worksheet 2 --- then I'd like to List the individual results
in Worksheet 2 Col "F" as well.
Worksheet 3 Col "A" has values that I want to count from worksheet 4 Col "A"
and place the total in Worksheet 1 Col "F" as they relate to Worksheet 3 Col
"B" & Worksheet 2 Col "C" and back to Worksheet 1 Col "A".

Now that I have confused everyone, I have detailed it in a layout below to
hopefully explain it better than what I could do in words.


Worksheet1

.......A..........B.......C........D...........E.. ..........F

1.....23a.....???......Y......WS2#....WS3#....WS4#

2.....23b.....???......Y......WS2#....WS3#....WS4#

3.....23c.....???......Y......WS2#....WS3#....WS4#

4.....23d.....???......n.........-...........-.............-

5.....23e.....???......Y......WS2#....WS3#....WS4#

6.....23f.....???......n..........-...........-.............-

7.....23g.....???......Y......WS2#....WS3#....WS4#



Worksheet2

.......A.........B........C.........D........E.... ...F

1.....23a.....???.....ABC......???.....???.....???

2.....23a.....???.....WER.....???.....???.....???

3.....23a.....???.....XYZ......???.....???.....???

4.....23b.....???.....SDF......???.....???.....???

5.....23b.....???.....JKL.......???.....???.....?? ?

6.....23c.....???.....ASD......???.....???.....???

7.....23e.....???.....LMN......???.....???.....???



Worksheet3

.......A..........B.........C........D........E... .....F

1.....234.....WER.....???.....???.....???.....???

2.....798.....WER.....???.....???.....???.....???

3.....008.....JKL.......???.....???.....???.....?? ?

4.....123.....JKL.......???.....???.....???.....?? ?

5.....798.....JKL.......???.....???.....???.....?? ?

6.....654.....ABC......???.....???.....???.....???

7.....888.....XYZ......???.....???.....???.....???




Worksheet4

.......A.........B........C.......D........E...... ..F

1.....234.....???.....???.....???.....???.....???

2.....234.....???.....???.....???.....???.....???

3.....234.....???.....???.....???.....???.....???

4.....234.....???.....???.....???.....???.....???

5.....234.....???.....???.....???.....???.....???

6.....234.....???.....???.....???.....???.....???

7.....234.....???.....???.....???.....???.....???




Worksheet1 Results

.......A.........B........C.......D........E...... ...F

1.....23a.....???......Y......003.....004.....007

2.....23b.....???......Y......002.....002.....000

3.....23c.....???......Y......001.....000.....000

4.....23d.....???......n.........-.........-..........-

5.....23e.....???......Y......001....WS3#.....WS4#

6.....23f.....???......n.........-.........-..........-

7.....23g.....???......Y......WS2#....WS3#....WS4#




Worksheet2 Results

.......A..........B........C........D........E.... ....F

1.....23a.....???.....ABC......???.....???.....654

2.....23a.....???.....WER.....???.....???.....234, 798

3.....23a.....???.....XYZ......???.....???.....888

4.....23b.....???.....SDF......???.....???.....???

5.....23b.....???.....JKL.......???.....???.....00 8, 123, 798

6.....23c.....???.....ASD......???.....???.....???

7.....23e.....???.....LMN.....???.....???.....???


I hope I have provided enough info to get the help I am wishing for.


Thanks in Advance and for your Patience,
Rob