count the total number of instances on a date with conditions

[Vitalii]

Hi  everybody! I have an issue with formula.  I have a data table, something like this:

report_date          num          date_open          date_close

31.08.2023                 125g          31.08.2023   Null

01.09.2023                 h12          25.03.2023   01.09.2023

30.08.2023                 abv09         01.01.2023   30.08.2023

27.08.2023                 Uups3         27.08.2023   Null

31.08.2023                 12t          01.07.2023   31.08.2023

etc.

I need count the total number of records on each report_date and create new table.

as a result, get a summary:

report_date    Count_num

27.08.2023       4

30.08.2023       4

31.08.2023      4

01.09.2023      3

In excel I use formula:

countif(date1<=report_date;date2=“”)+countif(date1<=report_date;date2>=report_date) 

Can you help me recreate this formula in quick view?

Thanks 

[BrunPierre]

Maybe this?

=Count({<date1={"<=$(=report_date)"}, date2={"=Len(Trim(date2))=0"}>} num) + count({<date1={"<=$(=report_date)"}, date2={">=$(=report_date)"}>} num)

[Vitalii]

Thanks, for your response. Unfortunately it gives me a 0.

[PrashantSangle]

don't understand your expected output w.r.t. input? can you explain how those count come?

Also not sure why 27/08/2023 date is not there? also not sure how 02/09/2023 date come in output.

Explain your logic little bit more.

Regards,

Prashant Sangle

[Vitalii]

Sorry about 27.08 and 02.09 my mistake. Edit example for clarity:
rename date1 as date_open and date2 as date _close. 

My task is creating a table with a report_dates and count all ‘num’ on report_date with conditions that

(date_open “<=“ report_date and date_close is null)+ (date_open “<=“ report_date and date_close > date_reporting)

so far I've manage to count all “num” but on every date I have the same number of records.

thanks!

[PrashantSangle]

I am still not clear with output w.r.t. input. 

there is only one entry for 27/08/2023 then how your count_num is 4. 

which are those num? 

don't know but, I think I am still missing something

Regards,

Prashant Sangle

[Vitalii]

This is sum of num’s with conditions I write.

Nums counting for 27.08: 

01.09.2023                 h12          25.03.2023   01.09.2023

30.08.2023                 abv09         01.01.2023   30.08.2023

27.08.2023                 Uups3         27.08.2023   Null

31.08.2023                 12t          01.07.2023   31.08.2023

they all have date open <= report_date (27.08)

this record doesn’t count because it has date_open > 27.08 and it start counting then date_report =31.08.2023

31.08.2023                 125g          31.08.2023   Null