• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countif - Several Worksheets If less than 3 months ago.

I have a workbook with three worksheets for every week, so by now, almost six months into the year, there are lots of worksheets.

I want to count the number of times a worksheet is less than 3 months old. The date is in cell B3.

I know that I can use =COUNTIF('Sheet1'!B3,TODAY()-1)+COUNTIF('Sheet2'!B3,TODAY()-1) etc, but that is cumbersome. Is there a formula I can use that will count the number of sheets where cell b3 is less than 1 month ago?

Thank you.
 
Yes, you can do this using 3d references. This is where you use a reference like:
Sheet1:Sheet6!B3
which looks at all the cells B3 in the sheets including and between Sheet1 and Sheet6 as arranged in your sheet tabs at the bottom.
Problem is that not many worksheet functions work well with these 3d references.
However, I discovered that TOCOL does:

84192

Now all you need is a separate cell looking at that range:

84193

It does NOT work if you try and put the two formulae together:

84194

edit:
prompted by Peter's post, a single cell solution:
=SUM(--(TOCOL(Sheet13:Sheet16!B3)>=EDATE(TODAY(),-3)))
 
Last edited:
It does NOT work if you try and put the two formulae together:

View attachment 84194
The reason Pascal's last solution fails is a limitation of the COUNTIF formula in that it insists on range references whereas, logically, it should also be applicable to arrays. Applying the COUNT and IF separately works, though not quite as efficiently,
= COUNT(IF(TOCOL(Date3D)>EDATE(TODAY(),-3),1))

From there one could use LET to remove nesting and explain the steps
Code:
= LET(
    listDates, TOCOL(Date3D),
    startDate, EDATE(TODAY(),-3),
    inDate,    SIGN(listDates > startDate),
    SUM(inDate)
  )
or even wrap the entire thing within a LAMBDA function
= CountActiveλ(Date3D, 3)
 
Thanks very much for these suggestions.

Pascal's second offering works fine, providing I use the excel program in OneDrive. TOCOL isn't supported by my version of excel - 2013.

As the On-line version of Excel, doesn't allow macros I don't think that I can use the Macro option becuse of the lack of support for TOCOL. I'm afraid that Lamda functions are beyond me and I get an error.
 
The first point concerning Excel 2013. Place it in the trash can where it belongs; it is not fit for any purpose other than demonstrating the way spreadsheets used to be! That is deliberately contentious and it is true that many users are perfectly happy with legacy code. It might be worth a moment of consideration before you move on though.

The second point is picking up on your mentions of Macros. I have recently submitted a paper for the EuSpRIG 2023 Conference. What I show below, in the image taken from the paper, is a worksheet formula and the definition of a Lambda function within Name Manager. The key point, below, is that Excel has changed out of all recognition and few spreadsheet users would recognise the solution as being an Excel worksheet formula!

84195
 

Attachments

  • Dates3D.xlsx
    22.7 KB · Views: 4
Your final comment is correct. I don't recognise this as excel. It's just beyond me. As an amateur though, using using excel for the statistics required either for my golf club, or for own personal golf records. Excel 2013 usually does more than enough for me. And generally, I can figure a way to do what I want even if it does mean using a couple of helper columns as I have to do here whilst trying to get a rolling one month and three month average of scores at each hole. What I actually did was to put in cell A30 COUNTIF(B3,">"&TODAY()-30) which gave 1 or 0 on each sheet. In the helper column, I put If($A$30=0,0,F9) and dragged that down to F27. My one month average was then Sum('First Sheet: Last Sheet'!F9/A30 dragged down to A27. It's a bit cumbersome but it does the trick and just needed a bit of lateral thinking. I will keep the on-line verson with the TOCOL as whilst I can't use the macro contained within the worksheets, tfunction isn't lost when I upload it and download it. It just isn't available to me on laptap version of excel. I'm afraid I couldn't make head nor tail of the lambda. It's just too complex for me.
 

Attachments

  • desktop test.xlsm
    130.6 KB · Views: 6
I understand and it may not give adequate return on time invested for you to learn the new tricks. I think familiarity makes you grossly underestimate the complexity of what you already do though. The 'simplicity' of a traditional spreadsheet has something of finding the needle in the haystack about it, in that the individual pieces of straw may be viewed as simple but the haystack is still pretty difficult to find one's way through. With Lambda, I would probably start by defining a function
= Stablefordλ(strokes, par, handicap)

It may still be somewhat messy under the hood but it is done once, tested, and then is used on every match sheet for every player. You could even share it with other club members for use in their workbooks. It is more advanced conceptually but simpler in use.

By the way, the example I showed is a nightmare. It is one of a number of workbooks that I have written to take complex mathematical programming tasks simply to demonstrate that they can now be solved using Excel worksheet formulas.
 
Last edited:
With a bit of luck I might be able to return to this problem one day to try to refactor it to use one dynamic range to calculate the Stableford points for each player at each hole, using a single formula per sheet.
A second formula would aggregate results over the relevant matches. The catch would be to ensure that the aggregations are performed over points achieved by each specific player as identified by name.

Part of the first formula would be
= IF(Competition="medal","", IF(player>0, 2 + par - Nett, ""))
but there are other weird bits required to calculate Nett from strokes and handicaps.
Who ever dreamt this scoring system up must have had far too much time on their hands!
 
That would be kind of you but there are many hidden columns and formulas which whuch rely on scores in these columns and I would want to create a hornets nest for you.

Stableford is actually quite a straightforward system 1 point for a nett one over par, 2 points for a nett par, 3 for a birdie etc. Zero points for anything worse that nett one over par.
 
I have found some of the calculations including the lookup array on the extreme right of each worksheet. Instead of using hidden columns for the intermediate calculations I planned to use local variables within LET functions in the final Stableford formula. Once I remove the intervening columns the data for each of the 4 players forms an array which could be calculated as a single step.

All this is on the back burner right now because you use Office 2013 and there is not a hope in hell of doing anything worthwhile with legacy (some might say obsolete) code.
 
Back
Top