Help with excel spreadsheet please

Heedtheadvice

This is when you ought to be doing  a bit of learning so you can work these things out for yourself!

Do an online search for COUNTIFS and you should be able to read the Microsoft description and come up with the answer.

However as  starter the COUNTIFS function counts all cells in  specified range that meet 1 or more criteria that you define in the function.

Tizerbelle has already given you the basis. Where the range to check for the matching criteria is all column A (A:A) and two criteria are following those defined ranges where the cell value in column A is both less than today and also greater than today minus 365. All that is entered in cells on column C ( plus a bit more following in column C to do the maths you required.)

Virtually all yo need to do is to add a bit more to the countifs function to select column B and the criteria "=1" thus counting if the cells are in the desired date range ( rolling year ) and recorded as a sick day.

Give it a try we will be happy to confirm what you do...

vix2000

Thank you for that.  I have searched extensively this last week but was not aware of the countifs formua so obviously not searching for the right thing!!!  Hopefully I can get it to work now.

GDB2222

I use an old version of excel, but do modern versions have a SumIf function, similar to countifs? 

Heedtheadvice

I am not sure as I too use an old version (2007) but usually functions are carried forward into newer versions. Sumif() is certainly in version 365.

victor2

SUMIF() and SUMIFS() are certainly there in Excel 2010 (my version!) and on.

They would do the job, with the extra advantage that you wouldn't need an entry for every day, just the total number of sick days between two dates, and it will sum them.

rmg1

It might be worth investigating dynamic named ranges as well.
You should be able to set up the named range to only refer to the cells you want and use those names in your formulae.
You'll need two named ranges:-
1) the dates you want
2) the other column which contains the sick days (assuming 1 or 0)