Forum Discussion

trevormorris's avatar
trevormorris
Copper Contributor
May 12, 2020

output a sum for variable precise limit

Dear All   Not sure if this can be done, but if someone has an idea, it would be very helpful. I have downloaded a years data from temperature probe into excel, what i want to do is find a total t...
Formulas and Functions
trevormorris's avatar
trevormorris
Copper Contributor
May 13, 2020

bhushan_z 

 

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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 13, 2020

trevormorris 

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.

 

  • trevormorris's avatar
    trevormorris
    Copper Contributor
    May 13, 2020

    SergeiBaklan 

    Perfect

    This has sorted it, thanks for your help 

    (also thanks to the others who have had a look at this problem)

     

    Trevor

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 13, 2020

      trevormorris 

      Trevor, you are welcome, glad to help

Resources