Formula help - adding totals from multiple sheets

[travelbug]

Hi all!

I haven't been able to create a formula that works for the following issue. I'd greatly appreciate any help you can provide!

I have four worksheets in my workbook. They are named Total, David, Jane, and Steve.

The David, Jane, and Steve worksheets have a three-letter code across each column (i.e. cell B2 is coded "ASF", cell C2 is coded "DMT" and so on). The rows beneath each code contain numbers. Each row is a different month (i.e. cell A3 is July, cell A4 is August, and so on).

The Total worksheet has the three-letter codes listed vertically down column A (i.e. cell A2 is ASF, cell A3 is DMT, etc.). Each column is a different month (i.e. column B is July, column C is August, etc.).

I want the cells on the "Total" sheet to automatically add up the totals from the other worksheets. For instance, I want cell A2 (the "ASF" row) to go through the other sheets and add the numbers under the ASF column from each sheet. The ASF column will not necessarily be in the same order on each sheet. Therefore, I want to use "ASF" as the criteria.

What formula will help me accomplish this?

Thanks again!

 

[doublej_jj]

Learn sumproduct - It will be your friend.

On Name Tab - B2:Z2 is 3 letter codes and A3:A14 is months
On Total Tab - A2 =ASF and B1=July

On Total Tab in cell B2
=sumproduct((David!$B$2:$Z$2=$A2)*(David!$A$3:$A$14=B$1)*(David!$B$3:$Z$14)) + repeat for Jane and Steve

Give it a shot.

Jeff