sum up days using only existing Excel formulas-CodePudding

I would like to sum up date periods and sum the days per item. The input data will grow over time and new item categories can appear, so the items (number of rows) that show in the expected report can not be "hardcoded".

The input parameter is the from and to date that determines the period that must be considered. You can imagine this as a moving date window on the input data grid.

I am a Java programmer and I am sure that I can write a proper SQL that groups and sums the data and generate the result. And I can write a Java program too, that does the job, but I really want to do this calculation from Excel.

Is there any way to generate the report by using only a combination of existing MS Excel formulas without writing any Visual Basic code (macro)?

If yes, then could you please put me in the right direction and tell me which formulas I can use? Then I can figure out how to use the formulas.

I hope that this helps to understand better what I would like to have:

CodePudding user response：

Try:

Formula in F3:

=SUM(COUNTIFS(C:C,E3,A:A,"<="&SEQUENCE(H$2-F$2,,F$2),B:B,">="&SEQUENCE(H$2-F$2,,F$2)))

Note that range references that take whole columns will take long to process all data. The above will work even with overlapping dates.