Trying to setup a case review reminder plus total values

[Nickpop]

Hi guys

I was wondering if anyone would have a simple solution for the following

I have attached a sample data sheet for a business for people in a market who give out samples and want to be reminded of when to contact them again to see if they want to try the product again.

I have 2 tabs.... Data & Dashboard. I would like to keep all my data items on a seperate tab from where the dashboard will very quickly tell me what i want to know.

The main issue i have here is trying to work out a formula that takes todays date, looks at date of review, and if its after that date then it flags up and calculates it as 1. (if it was to all go RED in the data, then that would be good)

I am also looking very simple count formulas for the remaining part of my query.

Help please
thanks
Nickpop

[AliGW]

Several variations of this:

=SUMPRODUCT((YEAR(Data!$D$3:$D$11)=2019)*(Data!$E$3:$E$11="OPEN"))

and this:

=SUMPRODUCT((YEAR(Data!$D$3:$D$11)=2019)*(Data!$E$3:$E$11="CLOSED"))

and this:

=SUMPRODUCT((Data!$C$3:$C$11="Cheese")*(Data!$E$3:$E$11="OPEN"))

and this:

=SUMPRODUCT((Data!$C$3:$C$11="Cheese")*(Data!$E$3:$E$11="CLOSED"))

plus this:

=COUNTIFS(Data!$F$3:$F$11,"<>",Data!$F$3:$F$11,"<"&TODAY())

And this for your conditional formatting rule:

=AND(F3 < > "",F3 < TODAY())

[Nickpop]

Thank you so much for this AliGW

Would it be possible to expand on the conditional formatting so that that the whole of the row is highlighted red if the date in Column F in past todays date?

[AliGW]

Yes - just change the Applies To range in the rule to include all columns and change the rule to this:

=AND($F3 < > "",$F3 < TODAY())

Range: =$B$3:$G$11

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.