I'm trying to sum values that are part of a frequency.
For example, in column A I have values that are either 0, or any other number. I would like Excel to sum values that are part of a frequency, for example, a set of 2 values in consecutive cells that are not zero. If there are 3 consecutive values it’s a different frequency (frequency of 3). Frequencies are separated with zero's.
Example: here is a set of values in column A, from A1:A17
There are 3 sets of 2 consecutive cells.
In this case, the formula's result should be 21 (the sum of all the values that are in the set of 2 consecutive cells).
I know how to make excel count the number of sets, but i can’t make it sum the values.
Any help will be great!
Thanks a lot!
Thanks for your help again!
Your formula works, but I forgot to mention a few things (sorry, I’m new to forums...).
I have a sheet with thousands of row's of values. Each row can either have a value of zero, or any other number.
The first step that I did was to count the number of times that consecutive cells appear. The count is made for any number of frequency (2 consecutive cells, 3 consecutive cells… etc.)
The formula I used is:
This formula counts how many times I have 2 consecutive cells with values that are not zero.
What I need to do now is SUM the values that are part of each frequency. For example, SUM of all the values that combine all the 2 consecutive cells in the give range.
It's pretty gnarly stuff - volatile, slow, complicated. If you're using a version of XL prior to 2007, the SUM(IFERROR(... part of the formula would have to be changed to SUM(IF(ISERROR(... and would basically end up twice as long.
I confess I'm not the best at these FREQUENCY() formulas, so it might well be that it can be improved upon.
Revised (.xlsx)workbook attached with both the pivot table and formula solutions.