Sum Values in a Table Matching Specific Row and Column Values

Hey everyone! I've been coming here for quite a while and I can say that I've learned a lot just by reading other posts, so first let me say thanks to everyone that contributes! I'd love to contribute to the forums myself as I gain experience. Now, for my question/problem that I've come here today for:

I have a workbook with multiple tabs that contain various tables full of data from different sources. I'm summarizing everything on the first tab in different columns depending on what I need to show. I'm breaking things down by month and each cell pulls in data from somewhere to summarize the activities. I have one tab containing the information I need and right now it's all included in my monthly breakout where this formula is used:

=IF(IFERROR(SUM(INDEX('Report'!$G$2:$V$20000,,MATCH("001/2014",'Report'!$G$2:$V$2,0))),0),SUM(INDEX('Report'!$G$2:$V$20000,,MATCH("001/2014",'Report'!$G$2:$V$2,0))),SUM('Annual Plan'!$B$22+'Annual Plan'!$B$23))

The data on the Report sheet has different information in columns A through F and then G through V give the dollar amounts by period. What I want to do is to isolate a few of the items where the value in column A is a specific value and then also exclude them from the cells where the above formula is being used.

So, just as an example...on my summary view I have E1:E12 showing January through December and the total values for that month. Let's say that right now, E1 shows $100,000 but includes $10,000 that I want to isolate in F1:F12 (depending on the month). So now, E1 would show $90,000 and F1 would show $10,000. Hope that all makes sense! I attached an example with the Report tab showing random data in the layout that I get from my reports and the Summary tab showing a simplified view of what I'm doing on my summary tab.

If you need more information on anything, let me know.

I really appreciate any input and look forward to your suggestions/solutions!

Dustin

Try this out, using named ranges for the months, and a condition formula to sort the data to be included (14600 or 14462 etc.) from the rest.

Sum Values Using Row and Column Data - with new formula.xlsx

Rob,

Thanks so much for the help! This solution works and I will see if I can incorporate this formula into my file on Monday when I get back to work. Could you explain a bit about why you decided to use the INDIRECT and SUBSTITUTE formulas? I just want to make sure I understand when it is best to use these. Thanks again!

Dustin

Originally Posted by Rob Kennedy

Try this out, using named ranges for the months, and a condition formula to sort the data to be included (14600 or 14462 etc.) from the rest.

Attachment 331158

Rob,

I see that you added a column to the report which is something that I won't be able to do. These are embedded into the document and connected to our financial system so the output must remain the same. Do you know of anything that would work without adding a column?

Thanks,

Dustin

Yes, that works and makes sense. I should become more familiar with named ranges! One additional question regarding this formula...how do I ensure that it works from year to year? For example, when the new fiscal year starts, I'd prefer that the formulas work automatically with the new reports (October 2014 through September 2015) without having to change the named ranges. This is important because I have multiple workbooks (one for each program that I am managing) and it would require a lot of work to ensure that everything is functioning properly. Everything else (in theory) will work in the new fiscal year without changing anything. Thoughts on that? Thanks!

Something I just noticed while working through some of the reports...while the values always start in column G, they aren't necessarily the first month of the fiscal year. For example, one report might span from G to V and include all 16 periods but another report might not have data for all periods so it'll run from G to N where the values in G are period 3, H are from period 5, I are from period 6, and so on. So, going back to the formula that I originally used (because it seems to do what I need it to)...how might I add an additional piece to that to have it look at both the periods AND the values in column A? I was trying to get an INDEX MATCH MATCH formula to work but couldn't exactly figure out how to make it operate. I'll keep experimenting but maybe someone has a suggestion on how to accomplish this? Thanks!