Forum Discussion
output a sum for variable precise limit
If I understood ur problem correctly, u can use COUNTIF or COUNTIFS function.
for example in 1st sheet (All Data Points) u can use below formula
=COUNTIFS($D$1:$D$576,">25")
These function would work, but would only give me the total of the whole sheet (have ~49K rows). What i want is the sum for the individual totals over 25. So want a sum output for rows 54-172, then the next one in this example would be rows 200-214. As you see the number of rows per occurrence above 25 is variable.
Once these output have been achieved, can then filter sheet to get only the outputs required.
Hopefully this explains better
Trevor
Another variant, if without dynamic arrays and Power Query, add helper column as here
in D2 0 or 1 depends on condition, in D3
=IF(D3<=25,0,IF(D2>25,E2,MAX($E$2:$E2)+1))and drag it down till end of the range, countif ranges after that as on the right.
Difference with your sample since it's not clear you count all >25 or all >=25.
Perfect
This has sorted it, thanks for your help
(also thanks to the others who have had a look at this problem)
Trevor
Trevor, you are welcome, glad to help
trevormorris Perhaps like in the attached workbook (from cell L54), provided that your version of Excel supports the FILTER function. Note that I inserted a column to combine the date and the time, but that I did not use your extra column where you calculated ">25".
trevormorris hmm, u need to define ur requirements in COUNTIFS. COUNTIFS can take multiple conditions, for example you can have ">25" as condition 1, then 'time' as condition 2, then date as condition 3, etc.
If you can share precise logic, I can help. It won't work without precise logic.
In case u want calculate for random rows, u need to apply formulas for each 'section of rows' separately.
