Multi-Worksheet Sumproduct & Listing

[Rob]

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.......???.....???.....008, 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

 

[Sheeloo]

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.

 

[Rob]

Thanks very much for the reply and attempt to help me!

I think I'm doing something wrong because when I use what you provided I get
the below results:

=SUMPRODUCT(--(Sheet2!$A$2:$A$2003=Sheet1!$C4)) = 2 -- GOOD/Perfect!

=SUMPRODUCT(--(Sheet2!$A$2:$A$2003=Sheet1!$C4),--(Sheet3!B$3:B$2003=Sheet2!$C2)) = Value Error -- Should be 3

I almost wonder if I should be trying to count an indexing result but then
I'm out of my league here. :/

 

[Sheeloo]

You are getting an error as the size of the two ranges have to be same (size
of B3:B2003 is different from A2:A2003)...
Use this
=SUMPRODUCT(--(Sheet2!$A$2:$A$2003=Sheet1!$C4),--(Sheet3!B$2:B$2003=Sheet2!$C2))
which will give you the intersection of the count not the union...
in other words it will count the rows where Col A matches C4 AND Col B
matches C2... it will not add the counts...
If you want the additon then use