Thread: Sum of values in frequency's

1. Registered User
Join Date
Mar 2010
Posts
30

Unanswered: Sum of values in frequency's

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!

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,

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...
Last edited by Colin Legg; 03-29-10 at 05:04.

3. Registered User
Join Date
Mar 2010
Posts
30
Hi Colin,
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.

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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...
Last edited by Colin Legg; 03-30-10 at 06:40.

5. Registered User
Join Date
Mar 2010
Posts
30
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!

6. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Hi,
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...
Last edited by Colin Legg; 03-31-10 at 00:22.

7. Registered User
Join Date
Mar 2010
Posts
30
Hi,
Wow, it is complicated.