I have a huge table with data that I want to query. I can look up individual values using a combination of Index and Match but when I want to sum a range using Index / Match as references, it adds up the tow values defined as the range rather than the range itself.
The top row of the table are Months so if I want to sum 12 months, the formula i developed is: =SUM(INDEX(ELE!$A$1:$AQ$5;MATCH(Report!$A4;ELE!$A$1:$A$5;0);MATCH(Report!B$4;ELE!$A$1:$AM$1;0)-11);INDEX(ELE!$A$1:$AQ$5;MATCH(Report!$A4;ELE!$A$1:$A$5;0);MATCH(Report!B$4;ELE!$A$1:$AM$1;0)))
But this adds up the 1st and 12th month, not the range. Any insight?
???it adds up the tow values
To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.
Doing this will ensure you get the result you need!
Hi,
I'm guessing what you are trying to achieve can probably be done using SUMPRODUCT or maybe even SUMIFS but I think it would really help to see a sample workbook.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks