Sum of values in frequency's

roeepa · #1 (**permalink**) 03-28-10, 10:51

Hello,
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
0
0
1
2
0
3
4
0
1
3
5
0
5
6
0
1
0
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!

Colin Legg · #2 (**permalink**) 03-28-10, 20:16

Hi,

For your example...

Firstly, insert a header row at the top. Give the column A header a 0 marker to start, so your sequence looks like:
0
0
0
1
2
etc...

In B2 add in this formula and fill it down the column:
=(A3>0)*(A4=0)*(A1=0)

Then the formula to sum all pairs of numbers greater than zero is:
=SUMIF(B2:B18,1,A2:A18)+SUMIF(B2:B18,1,A3:A19)

Hope that helps...

roeepa · #3 (**permalink**) 03-30-10, 04:23

Hi Colin,
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:

=COUNT(IF(FREQUENCY(IF($A$2:$A$31<>0,ROW($A$2:$A$3 1)),IF($A$2:$A$31=0,ROW($A$2:$A$31)))=2,2))

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.

I’ve attached an example file.

Thanks again for your help with this!!!
Roee.

Colin Legg · #4 (**permalink**) 03-30-10, 05:32

Hi,

I've been a keen advocate of array formulae over the years, but they have a few drawbacks:

They are slow to calculate.
They are difficult to understand (and therefore difficult to maintain).
They are hard to problem-shoot.

I'm not saying that you should necessarily avoid a complex array formula to get your sum, but at least consider the following alternative which uses a couple of helper columns and a pivot table.

In B2 (the 'grouping' column), this formula copied down:
=IF(A2,SUM(B1,1),)

In C2 (the 'count' column), this formula copied down:
=IF(B2*B3,C3,B2)

Finally, to get a weighted count (to derive the count of each frequency):
=IF(C2,1/C2,0)

Then it's just a case of summarising the count and sums for all frequencies in a simple pivot table. I think this is a fairly straightforward solution.

Hope that helps...

roeepa · #5 (**permalink**) 03-30-10, 14:19

Colin,
Thanks a lot!!! Its working!
Just out of couriosity, how would the array formula look for something like that? I tried replacing the COUNT at the begining with SUM, thinking that it'll work...

Thanks again for your great help!

Colin Legg · #6 (**permalink**) 03-30-10, 17:40

Hi,

Quote:

Just out of couriosity, how would the array formula look for something like that? I tried replacing the COUNT at the begining with SUM, thinking that it'll work...

This comes back to the point I made earlier that these formulas are pretty tough to understand!

Taking the attachment in post #4 as our example, for a 'frequency' of 2 the count and sum in H8 and H9 (in the pivotable) are 288 and 3966.

To get the total count of 288, a variation of your FREQUENCY() formula:

Code:

{=SUM(--(FREQUENCY(
    IF($A$2:$A$2161<>0,ROW($A$2:$A$2161)),
        IF($A$2:$A$2161=0,ROW($A$2:$A$2161)))=2))}

To get the total sum of 3966, in XL 2007 the best I could do was along the lines of:

Code:

{=SUM(IFERROR(N(OFFSET(A2,(IF(FREQUENCY(
    IF($A$2:$A$2161<>0,ROW($A$2:$A$2161)),IF($A$2:$A$2161=0,ROW($A$2:$A$2161),0))
        =2,(ROW($A$2:$A$2161)-{1,2}),""))-ROW(A2),0,{1,2},1)),0))}

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.

Hope that helps...