Perform caluclation on certain days of the week

[AlexHoran]

Hello board, I have had a good look for this solution but have not been able to find one.

I wish to have excel perform a COUNTIF on certain days of the week. For example I have a list of values (lets say in I30 -> I120) and on Mondays I want my excel workbook to do a COUNTIF couting within the range for a value (lets say "work") it will then return the amount to a cell (B4). However on every other day of the week I don't want this formula to run.

Can anyone give show me how I can have excel perform this action?

I don't care how many times on Monday the COUNTIF function runs, I just want it to only run on monday.

Thanks,
Alex

 

[NBVC]

Like this?

=IF(WEEKDAY(TODAY())=2,COUNTIF(I20:I120,"work"),"")

 

[AlexHoran]

RE:Like this?

I started of with something like this, but what I found was that if today was not Monday then the forumla would clear the cell. What I would like on the other days on the week is for the last value entered by the formula to be kept (untill the next monday).

Does that make sense?

Thank you very much for the quick reply.

 

[NBVC]

I don't think that can be done because the formula will evaluate everytime a calculation is run on the sheet. You can't make it "remember" what was there until a certain event occurs.

You will probably need someone to write you a macro for this.

 

[Yogi Anand]

Hi AlexHoran:

Try ...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If WeekDay(Date) = 2 Then [K30] = Evaluate("=countif(I30:I120,""work"")")
End Sub

 

[AlexHoran]

ahhh that seems to do the trick. Thanks for the advice Yogi