Using Cell Contents Within a Formula

[Tikey]

I have been using the following formula which works fine, but I would like to be able to change the Year part of the dates and wondered whether there is a way of referencing a cell value which would contain the year and therefore make it easier to change, I was thinking about the Indirect function but I'm not sure how to use this or whether that would be the best way. I use 52 instances of this formula ( 1 for each week of the year ) and don't wish to have to frequently edit all of them.

=SUMPRODUCT((Records!$F$2:$F$40000>="2017-1-1"+0)*(Records!$F$2:$F$40000<"2017-1-7"+1)*((Records!$I$2:$I$40000>0)+(Records!$J$2:$J$40000>0)+(Records!$K$2:$K$40000>0)))

Thanks for any help.

 

[Joe4]

Not sure I completely understand your question, but if you are saying that you would like the year part here:

[COLOR=#333333]=SUMPRODUCT((Records!$F$2:$F$40000>="[/COLOR][COLOR=#ff0000]2017[/COLOR][COLOR=#333333]-1-1"+0)...[/COLOR]

to come from a cell, that should be easy enough to do.

Let's say that the year is in cell A1. Since you appear to be trying to change it to a date anyway, I would do it like this:

[COLOR=#333333]=SUMPRODUCT((Records!$F$2:$F$40000>=[/COLOR][COLOR=#ff0000]DATE(A1,1,1)[/COLOR][COLOR=#333333])[/COLOR][COLOR=#333333]...[/COLOR]

Note that the format of the DATE function is DATE(year,month,date)

Is that what you were looking for?

 

[Tikey]

Thanks for the reply.

Yes thats what I was after, I have edited the formula as below and that appears to work fine, I assume I have made the changes as required ?

=SUMPRODUCT((Records!$F$2:$F$40000>=DATE(R3,5,15))*(Records!$F$2:$F$40000<=DATE(R3,5,21))*((Records!$I$2:$I$40000>0)+(Records!$J$2:$J$40000>0)+(Records!$K$2:$K$40000>0)))

 

[Joe4]

Yep, looks good!

 

[Tikey]

Thanks for all your help.

 

[Joe4]

You are welcome.
Glad I was able to help.