Counting values occurring only on or before a date

English
,
#1

I have a spreadsheet created originally in Excel, and a formula that worked in Excel isn’t working in Calc. I’m hoping for help to adapt the formula.

The spreadsheet has columns headed up with dates of meetings. For each entity listed in the rows, one of four values is listed under each date: Y, N, Ap, N/A.

I’ve filled out all the cells with Y for the whole year. N, Ap and N/A are rarer and so it’s easier to enter only those as the year progresses. I was able to get Excel to count only those values in the rows that occurred in date columns on or before the current day, with the following formula:

=SUMPRODUCT(($H$5:$BA$5<=TODAY())*($H54:$BA54=A$5))

with A$5 being the cell containing the value needed to be counted.

Calc doesn’t recognise this formula. It’s counting all the values under all the columns. This means that it’s counting all the people ‘present’ in many meetings that haven’t happened yet.

Unfortunately if I delete all those Ys in the future cells, it messes up the various formulae I’ve got further down that count and analyse all the raw data in this first table.

So I’d like to come up with a way to keep all that pre-entered data and count only that for today and before today.

Can you suggest a new formula to do this?

#2

Here is an example that works with your exact formula, please look at it and tell me what I am not doing correctly:
SumproductFromExcel.ods (11.0 KB)

#3

Hi Steph,

Thanks for taking the time to help. Your file works, as you say. I’ll upload mine, which looks the same as far as I can see, but which DOESN’T work. Maybe you can see why, where I haven’t been able to.

TODAY not working.ods (17.8 KB)

#4

The range should extend to column BK, but it does not.

1 Like
#5

Thanks PKG. You’re right. It didn’t. Now that that is fixed and the file re-uploaded, can you see what else might be wrong with it? I still can’t understand why it’s counting the future Ys

#7

There is no future data in the file.
The current year is 2024

#8

I discovered that independently, too. Well-spotted.

#6

It was the YEAR! In the dates. It was a year in the past; 2023. Now that it’s been altered to 2024, the formula works. Detail. This is a software that rewards attention to detail.
TODAY working.ods (17.7 KB)

Thank you both very much for your input. I wouldn’t have found the oversight without it.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.