*REDACTED* - I feel I had to much sensitive info in this original post, see replies below for info .
*REDACTED* - I feel I had to much sensitive info in this original post, see replies below for info .
Last edited by hottoddy; 06-17-2022 at 11:12 AM.
Please can you share a santised sample of the worksheet itself? There are many possible reasons why you might not be getting the expected results, but its very difficult to diagnose from a screenshot.
<<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts
Yes, please read the yellow banner at the top of the page for attaching a sample workbook.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. As you are new here, I have done it for you this time.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Hi All! First, thank you for your replies - I should have read the yellow banner at the top and probably familiarized myself with the other rules you have pointed out. I apologize for that. I have attached a sanitized version of the spreadsheet. On the "Table" tab, I have pasted only the values into the table rather than leaving the formulas that were there previously. This table is looking up the Cost of something at the start of a process and then evenly applying that cost over the course of 8 months for each Category and Phase, so each unique value in the columns appears exactly 8 times. In the Return tab, I am trying to retrieve the sum of values for each Category and Phase at a specific period. I need the sum, as there will be additional "starts" for each category/phase in the Table tab previously mentioned. The "Starts" or Start Period, is technically the column headers for the data set in the Table tab, but I think that this should be irrelevant beyond its use for creating the actual data set in this tab.
Try this in cell AO11 and copy down/across
=IF(AO$6>$B$3,SUMPRODUCT((Table!$K$34:$DU$1373)*(Table!$H$34:$H$1373='Cash Flow'!$B11)*(Table!$G$34:$G$1373='Cash Flow'!$C11)*(Table!$E$34:$E$1373='Cash Flow'!AO$5)),0)
Does this get you to the expected results?
I'm not sure why I can't figure out how to reply to this thread... twice I've tried to post that I actually solved this issue by creating a subtotal for each row in a column at the end of the table and then just using a basic SUMIFS to achieve the same result. AskMeAboutExcel, your solution also works AND is closer to what I was trying to achieve initially - so thank you very much. Achieving this through SUMPRODUCT was how I thought it should work, regardless of my solution, so getting to see how you constructed the formula is extremely beneficial for me. Thank you very much!
no problem, glad you were able to solve this. Using helper columns /calculation often isn't considered 'as elegant' as using a more complex formula, but in many cases it makes for simpler formulas that are not only easier to code, but more transparent to follow and check
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks