# Perform caluclation on certain days of the week

#### AlexHoran

##### New Member
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

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### NBVC

##### Well-known Member
Like this?

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

#### AlexHoran

##### New Member
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

##### Well-known Member
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

##### MrExcel MVP
Hi AlexHoran:

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

#### AlexHoran

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

Replies
0
Views
222
Replies
0
Views
385
Replies
0
Views
266
Replies
5
Views
191
Replies
3
Views
209

1,195,720
Messages
6,011,292
Members
441,599
Latest member
Jribas

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back