Combine data

[devilfish]

I'm really not sure if what I'm trying to do is possible. Logically (at least to me), it should be. Let me begin by explaining.

I have 3 worksheets inside my file that I'm trying to work with and incorporate. The worksheets are titled "Referees", "T-Shirts" and "T-Shirt Totals". Each worksheet contains the following information: Team#, Name, and t-shirts sizes from YS - A4X. The range of columns is from A to M. Two worksheets only use 98 rows while my "Referees" worksheet uses 224. Now on to my problem.

What I'm trying to do is combine data from "T-Shirts" and "Referees" based on whether or not information in Column A - Team# for both sheets matches that of columnA - row2 of "T-Shirt Totals". I'm trying to get the "T-Shirt Totals" sheet to scan a range for each size and report back a total if the "Team#" is the same for both "T-Shirts" and "Referees". Each size t-shirt should have it's own total depending on whether the "Referees" and the "T-Shirts" match the "Team#" selected. I need to compile a total for all t-shirt sizes based on each team.

Here's what I'm using:

=IF(AND('T-Shirts'!A2:A98='T-Shirt Totals'!A2,Referees!A2:A224='T-Shirt Totals'!A2), 'T-Shirts'!J2:J98+Referees!J2:J224, "0")

Sometimes for some rows it reports the appropriate number, but most of the time, it doesn't report anything. I might be thinking to "databasey" for Excel, but this file was set-up by my mom for a non-profit youth soccer organization and I'm just trying to streamline it. She has to turn the file in tonight and unfortunately I just got to look at it last night. I'll apologize now for my bulky code. I'm not an advanced programmer. I've included the file because I know that I didn't explain myself properly.

Thanks for any and all help provided!!

Cheers,
DF

[BigBas]

try this:

on the "T-Shirt Total" sheet, in cell C2, enter this formula, and fill it to the other cells in the range:

=INDEX('T-Shirts'!$A$2:$M$98,MATCH('T-Shirt Totals'!$A2,'T-Shirts'!$A$2:$A$98,0),MATCH('T-Shirt Totals'!C$1,'T-Shirts'!$A$1:$M$1,0))+SUMIF(Referees!$A$2:$A$224,'T-Shirt Totals'!$A2,Referees!C$2:C$224)

Let me know if it works.

[devilfish]

try this:

on the "T-Shirt Total" sheet, in cell C2, enter this formula, and fill it to the other cells in the range:

=INDEX('T-Shirts'!$A$2:$M$98,MATCH('T-Shirt Totals'!$A2,'T-Shirts'!$A$2:$A$98,0),MATCH('T-Shirt Totals'!C$1,'T-Shirts'!$A$1:$M$1,0))+SUMIF(Referees!$A$2:$A$224,'T-Shirt Totals'!$A2,Referees!C$2:C$224)

Let me know if it works.

I did as you said and it works much better than my horrible code. However, the sheet isn't providing me with a total of both "T-Shirts" and "Referees" in some fields. It gives me one or the other and in some cases, a total of two, but not all three fields in the "Referees" sheet.

I really, REALLY appreciate the help!!!

*Edit* Removed the file only because it contains contact information which should be private. I totally forgot to remove that information. Not that anyone here would take that information and inappropriately use it, but I need to be sensitive to that material.

[BigBas]

Im not sure that I fully understand what you want to do. The way I have it setup is that it takes the total per team from the "T-Shirts" sheet, and adds that to the total and adds that to the total per team from the referees page.

If you need it to calculate something else, or calculate differently, please let me know and I will try to accomodate.

Ideally, if you told me a few of the cells that are not working correctly, perhaps I can work in reverse to figure out the problem.

[devilfish]

BigBas, I really appreciate the help. The formula that you devised works well, but in some cells, it doesn't seem to add the totals from "T-Shirts" and "Referees". An example is in "T-Shirt Totals", cell I5. There are two referees for BU08-1345A that are AL size t-shirts, but in cell I5, it's not calculating the totals. If you look to cell J5, it's not calculating that size either.

Did I explain that properly? I believe that you're doing what I'd like to do, but it's not computing for some reason. Man do I need to touch-up my Excel knowledge. I feel really dumb right now.

Again, thanks very much for the help!!!

[BigBas]

that is weird. I went through, and noticed the mistakes that you noted. However, I re-entered the formulas into the cell, and filled it, and it seems to have worked. I wonder if the formula was copied incorrectly somehow or something. Either way, I have attached the workbook, with the formulas added. I went through some cells, and it looks correct. Let me know.

[vane0326]

Here you go just input formula in cell C2 and copy across and copy down.


=SUMPRODUCT(('T-Shirts'!$A$2:$A$98=$A2)*('T-Shirts'!$C$1:$M$1=C$1)*('T-Shirts'!$C$2:$M$98))+SUMPRODUCT((Referees!$A$2:$A$224=$A2)*(Referees!$C$1:$M$1=C$1)*(Referees!$C$2:$M$224))

[devilfish]

Here you go just input formula in cell C2 and copy across and copy down.


=SUMPRODUCT(('T-Shirts'!$A$2:$A$98=$A2)*('T-Shirts'!$C$1:$M$1=C$1)*('T-Shirts'!$C$2:$M$98))+SUMPRODUCT((Referees!$A$2:$A$224=$A2)*(Referees!$C$1:$M$1=C$1)*(Referees!$C$2:$M$224))

Thanks so much!! For almost all the columns, the formula computes correctly. In columns AL and AXL in sheet "T-Shirt Totals", the totals don't match what the other two worksheets total up to. They're only off by a few numbers, so I'm gonna see if I can find where the difference is. Again, thanks so much!!

Just for reference, what's the purpose of $ in the forumula?