complex SUMIF based on date and Y/N criteria

I work in sales and I get paid weekly on a Friday. I am often paid for one sales over the course of 4 months (1 month = 28 days) because they signed up on a payment plan and i get paid on cash collected. However there is the option to pay in full upfront as well.

In the attached doc you will find the spreadsheet I have created to log my commissions and keep track of how much I am due to be paid (and when) and how much I have actually been paid each month (which is where i need help!).

I will now detail each formula and formatting that I have created.

- Cell+28 days to calculate the next months payment
- Conditional formatting of dates. Blue to indicate payday, Red to indicate payday has not passed, Green to indicate that Payday has passed.
- SUMIF formula in cells Z2, Z3 and Z4, which calculates how much I am due to be paid based on the dates in columns AB and AC.
- SUMIF formula in cell AD2 which calculates whether the payment has been made based on Y in criteria column of SUMIF formula.

This last formula is where I need help please. I want to have the amount in cells AD2, AD3 and AD4 populate based on whether i've been paid (Y) but restrict it to the date range in columns AB and AC so that it shows monthly.

I want to just be able to look at the colours of the dates on a Friday, see blue, and log Y next to each payment and have the amount paid for the month populate automatically.

NB: 'Draft amount' is the payment i am due to receive and 'Draft date' is the date I am due to be paid.

Welcome to the forum.

I want to just be able to look at the colours of the dates on a Friday, see blue, ...

I see no blue - did you mean green? Unfortunately Excel formulae cannot use colour formatting.

Can you explain what is wrong with the formula you already have? If it's not producing the correct results, then you need to add your expected results manually to the workbook.

Sorry, but it's not immediately clear to me what you are after here.

Administrative Note:

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.)

Thanks - please provide a version of the workbook with the results you want mocked up manually instead of a non-working formula. Highlight these cells in YELLOW.

Sorry - could we please have a sheet that properly reflects the real scenario, not with Y appearing where it would not normally do so? Please adjust the dates if necessary. Solutions will be based on the sample data, so it needs to be realistic!

Try pasting this array entered formula** into cell AD2:

Formula:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
After entering the formula, as described above, drag the fill handle down to cell AD6
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.