I had to create a formula that would sum totals of product sales on different customer sheets (circa 80 customers) within the same work book where products were appearing on different rows for each customer. The normal 3d formula of the type ‘=SUM(Sales:Marketing!B3)’ does not work as the products are all shown on different rows in each customer sheet.
The solution I came up with is this
=SUMPRODUCT(SUMIF(INDIRECT(“‘”&sheets&”‘!”&”$a$10:$a$200“),$B4,INDIRECT(“‘”&sheets&”‘!”&”j$10:j$200“)))
A few points of explanation:
- Sheets is a name range where I listed (by macro) all the sheet names,
- A10 to A200 is where the product names appear on each sheet,
- J10 to J200 is where the data for January is on each customer sheet. subsequent months require the column reference to be K, L, M etc.
- The row lines used for product names A10 to A200 and data J10 to J200 must be identical – in this case I have used rows 10 to 200, with row 200 being set higher than the maximum number of rows used on any one sheet to ensure all the product values are picked despite the number of rows for each customer varying.
- B4 is the cell on the summary sheet with the product name. As only the column is fixed then the formula will recognise subsequent Products as you copy it down
- If you use formula evaluate (form the Formula Ribbon) you will see Value# errors at certain stages of the evaluation. do not worry this is just the formula recognising that one of the elements does not exist.
Excel Monkey 