Results 1 to 7 of 7
Thread: Sum of values in frequency's

032810, 11:51 #1Registered 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!

032810, 21:16 #2Registered User
 Join Date
 Sep 2008
 Location
 London, UK
 Posts
 511
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...Last edited by Colin Legg; 032910 at 05:04.

033010, 05:23 #3Registered User
 Join Date
 Mar 2010
 Posts
 30
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.

033010, 06:32 #4Registered 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 problemshoot.
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; 033010 at 06:40.

033010, 15:19 #5Registered 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!

033010, 18:40 #6Registered 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...
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))}
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))}
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; 033110 at 00:22.

033110, 04:04 #7Registered User
 Join Date
 Mar 2010
 Posts
 30
Hi,
Wow, it is complicated.
Thanks for all your help.
I'm using the other formulas that you suggested and its working great.
Have a good one!
Roee.