Help for Sumproduct function on excel

[Lorens29]

Hello,

Please help me find the Excel Formula to count number of Child ID has FALSE >=3 days in a month using Sumproduct function
I hope that someone can help me with the problem. I have attached an excel with the sample data.
Thank you to anyone who can solve this for me, and provide me with the excel logic to this, - please updated the table to show how
Many thanks once again. I hope my explanation was clear. I have googled and tried - but with little or no success.

 

[AliGW]

Cross-posted: https://www.excelforum.com/excel-fo...hild-id-who-has-false-3-consecutive-days.html

 

[Lorens29]

Not crossposted. Rhe first posted is about consecutive days. This is not consecytive days. Please read first

 

[Lorens29]

Please help me

 

[GraH - Guido]

Hi, Lorens29 I recognized this.
See attached. 2 solutions offered: a pivot (using an extra column in the data) or a formula.

EDIT: uploaded the correct the file now

 

[Lorens29]

Thank you. This is the solution.

Thanks

 

[Peter Bartholomew]

This is NOT the solution but it may be of interest to some.
What prevents me from offering it as a solution is that it uses Charles Williams 'SpeedTools' Add-in. The particular function it relies upon was written to support financial modelling 'corkscrews' using dynamic arrays. The syntax of the function is

ACCUMULATE( StartValue, [Factors] , [AddValues], [Opening/Closing] )

I have also used the LET function which is under beta testing within the Office365 insider's channel. The function has an alternating pattern of parameters with names, followed by the formula they refer to and finishing with the formula result, which naturally uses the names defined within the formula.


The consecutive absences are accumulated but the running total is re-set if the child in question attends (opening balance, minus 100%).

If anyone is familiar with the FAST standard for financial modelling
- Do not use names
- Do not use array formulas
- Do not use formulas longer than your thumb
- Do not break formulas over multiple rows

The sound of apoplectic fits ???