Return a cell value if a date is contained in a range of cells

[studio95]

Hello all,

I currently have a formula in a sheet which scans a row of dates and then populates another cell with the number of occurrences within the month selected in nominated cell. The current formula is as follows :-

=SUMPRODUCT(--(TEXT(AB3:AW3,"mmm\\yy")=$AX$1),MOD(COLUMN(AB3:AW3)-COLUMN(AB3:AW3)+1,2))

What I now need to do elsewhere in the sheet is perform the same scan for a date but instead of returning the number of occurrences return a value held in another cell..

In addition to this I also need the ability not only to select an individual month currently held in $AX$1 (I simply select the month required from a drop down list) but to also have the ability to select a number of months i.e a business quarter or indeed YTD

Any Ideas?

[Pete_UK]

Seems like you need an INDEX/MATCH formula to return data from another cell, but I can't suggest an actual formula to you as you have been very vague about where the date is located, which cells you want to search through, where the corresponding data to return is held, etc. Perhaps that is why no-one has replied to you in the six and a half hours since you started this thread.

Pete

[studio95]

Hello Pete,

Thanks for your note.

My Apologies as I thought I had been quite clear where the date was being held "select an individual month currently held in $AX$1" likewise the formula posted would have indicated that it is searching for an occurrence of a date between AB3 & AW3, the return data can be any cell you like as I would edit to make it fit with my needs...

Thanks for your help tho.

Paul

[Pete_UK]

You say that the individual month is held in cell AX1 and is selected by a drop-down. This could take many different forms, however. For instance, it could be Jan, Feb, Mar etc., or January, February, March, or 1, 2, 3, or 01, 02, 03, or Jan16, Feb16, Mar16, and so on. A month is not a date, so if you have dates in AB3:AW3 you will not be able to get an exact match unless you manipulate the value in AX1 in some way. Also, you do not state where the data to be returned is located - we could presume that it is in AB4:AW4, but that would only be a guess.

So, you need to give a lot more detail if you want us to help you. It is often easier to attach a sample workbook than to describe exactly how your data is laid out. To do this, click on Go Advanced below the edit window while you are composing a post, then scroll down to and click on Manage Attachments, and in the new window that pops up click on Browse then navigate to the file icon and double-click it, then click Upload, then Close Window, and then when you have finished composing your post you can click on Submit Post.

Hope this helps.

Pete

[studio95]

Hello Pete,

Thanks for your reply, apologies for the delay in responding.

the format of the date in AX1 is Jan-16, Feb-16, Mar-16 etc etc. The dates in the range of cells L3:AW3 are in the format on 1/1/16, 25/2/16 as I wanted to be able to select individual months from the filter list and having the dates represented in this way enables this so when clicking the filter it sorts them by month then by day. This is then repeated in every row from row from row 3 to row 3143

In relation to the data return cell this is likely to be in column AX for each row, but really could be in any column I choose but need it to return the value in cell CQ3 if there is a date in L3:AW3 that falls in the month selected in AX3

I have attached a sample of the workbook as requested.

Thanks again for your help, I hope my explanation make a bit more sense now?

Kind regards

Paul