SUM Across Multiple Columns, IF within specified Date AND date plus 12 Months

[DTK0902]

Hi All,
Unsure if this is possible, been trying a few different approaches this morning, but heres a description:


Range G6:AI2000 contain monetary calculations, based on a statement which only populates a value into the cell if the
Column A contains the date of the transaction,
Rows 3 and 4 contain the beginning date of the month and the end date of the month respectively.

What I would like to happen is, if the date in Column A equals the start date of the month (E.G Column H = May 2018), and the date within Column A is on or before the end date of that month, then sum anything in the Range of G:AI.


Unsure if this is possible, but any help is appreciated.


Thanks
Daniel

[dflak]

Sounds very possible. Could you please provide a sample workbook? Thanks.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Special-K]

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[DTK0902]

Hi,
Thanks for the reply, I've attached a cleansed version to this post.

The expected results are that ROW 2 should be populated with any the income generated from any sale within that month, ideally over the life of the contract.



Thanks in advance,
Daniel

[dflak]

Your date formats ar European for start dates and American for end dates. I changed the date ranges to "fit" the data.

Anyway, I think this is what you want. I converted the data into an Excel Table because Excel Tables "know" how big they are, so as you add data, you do not have to change the formulas. Also you can use header names in the formulas which makes it easier to understand what they are supposed to be doing.

[DTK0902]

Your date formats ar European for start dates and American for end dates. I changed the date ranges to "fit" the data.

Anyway, I think this is what you want. I converted the data into an Excel Table because Excel Tables "know" how big they are, so as you add data, you do not have to change the formulas. Also you can use header names in the formulas which makes it easier to understand what they are supposed to be doing.

Thanks for the reply, I cannot see an attachment though?

[dflak]

I failed to notice that it did not attach since it is now too big. Here is the compressed version.