I need to sum parts of multiple columns from another worksheet with multiple criteria, using index/match as one of the criteria. Basically I need a way to sum the initial column and two columns immediately preceding it
I need to sum parts of multiple columns from another worksheet with multiple criteria, using index/match as one of the criteria. Basically I need a way to sum the initial column and two columns immediately preceding it
In these instances a workbook example helps a lot. Just something simple with what your data layout looks like and your expected solution, or at least a good explanation.
Despite the high cost of living, it remains very popular.
Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!
Ok, so in the example I'm uploading, the column headers are dates, so for each date on Sheet2, I need to add up the values for the same date and the previous 3,12 months in sheet1 based on the criteria of the type of fruit(B2, criteria range 'sheet1'!Z2:Z7) and the date itself.
Also, each month the left most column is removed and a new column with the next month is inserted at the far right, so that's why I was thinking index/match is needed.
Hahah, oh man. I was so confused for a few moments. I didn't realize you had made the numbers random so each time I calculated the numbers changed on me.
Attached is a sample of a solution. You do have 2 lines with apple and this will only sum the first line and not all lines. Do you need it to be able to sum all lines?
Yes, it needs to sum all lines.
Are they at least next to each other all the time, or can there be other lines between them... cause that would make it a lot more difficult. If they are always right next to each other that is an easy fix.
Unfortunately they're scattered throughout hundreds of lines
Well, I would suggest either sorting the data first or you will probably have to resort to a vba solution.
Yeah VBA was my last resort...How complex do you think the code would be?
Let me know if this works for you. Explanation is in the workbook.
Last edited by Hawkeye16; 07-17-2014 at 07:59 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks