Summing groups

NBVC

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
<form>I am trying to help someone with a formula and I am stumped....

What I want is to sum any/all groups of numbers larger than 0 that are grouped in 4 or more....e.g. in my example D1:G1. The answer should be 150.... I'm trying this formula: =SUM(IF(FREQUENCY(IF(A1:L1>0,COLUMN(A1:L1)),IF(A1:L1=0,COLUMN(A1:L1)))>=4,A1:L1))

but getting the sum of all cells...<form>
<table x:str="" style="border-collapse: collapse; width: 576pt;" border="0" cellpadding="0" cellspacing="0" width="768"><col style="width: 48pt;" span="12" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" x:num="" align="right" height="17" width="64">25</td> <td style="width: 48pt;" x:num="" align="right" width="64">0</td> <td style="width: 48pt;" x:num="" align="right" width="64">0</td> <td style="width: 48pt;" x:num="" align="right" width="64">25</td> <td style="width: 48pt;" x:num="" align="right" width="64">50</td> <td style="width: 48pt;" x:num="" align="right" width="64">50</td> <td style="width: 48pt;" x:num="" align="right" width="64">25</td> <td style="width: 48pt;" x:num="" align="right" width="64">0</td> <td style="width: 48pt;" x:num="" align="right" width="64">50</td> <td style="width: 48pt;" x:num="" align="right" width="64">100</td> <td style="width: 48pt;" x:num="" align="right" width="64">50</td> <td style="width: 48pt;" x:num="" align="right" width="64">0</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" colspan="3" style="height: 12.75pt;" height="17">Sum of group of 4 or more</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">I get</td> <td x:num="" x:arrayrange="B5" x:fmla="=SUM(IF(FREQUENCY(IF(A1:L1>0,COLUMN(A1:L1)),IF(A1:L1=0,COLUMN(A1:L1)))>=4,A1:L1))" align="right">375</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Should be</td> <td x:num="" align="right">150</td> <td colspan="2" style="">(sum of d1 to g1)</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
</form>
</form>
<table x:str="" style="border-collapse: collapse; width: 768px; height: 126px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt; width: 48pt;" x:num="" align="right" height="17" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td><td style="width: 48pt;" x:num="" align="right" width="64">
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td class="xl22" colspan="3" style="height: 12.75pt;" height="17">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td x:num="" x:arrayrange="B5" x:fmla="=SUM(IF(FREQUENCY(IF(A1:L1>0,COLUMN(A1:L1)),IF(A1:L1=0,COLUMN(A1:L1)))>=4,A1:L1))" align="right">
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height: 12.75pt;" height="17"><td style="height: 12.75pt;" height="17">
</td><td x:num="" align="right">
</td><td colspan="2" style="">
</td> <td>

</td> <td>

</td> <td>

</td> <td>

</td> <td>

</td> <td>

</td> <td>

</td> <td>

</td> </tr> </tbody></table>
 
Last edited:

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
I can do this in long-winded way, I'm sure others will have a better system.

I'm assuming that the numbers start in cell a1. I'll use 3 rows of formulas that run below the original data as follows:

In cell a2
=(A1>0)

In cell a3
=IF(A1>0+B1>0+C1>0+D1>0=4,A1+B1+C1+D1,0)

in cell a4
=IF(A3=4,SUM(A1:D1),0)


Copy those formula across so that each number in your data has 3 rows of my formula below it.

To get the answer you want in cell M4 (which is the last cell below your data) use
=MAX(A4:L4)
 
Upvote 0
Thanks for that, but I would like 1 formula as I will have a larger set of data with many groups of 4.... this was a sample only.
 
Upvote 0
Yeah, thanks but that ain't going to work for me... I've got lots of cells filled out in row 1 and many groups of 4 or more, that I need to sum all together.

<form>
</form>
 
Upvote 0
For a clearer picture....

Excel Workbook
ABCDEFGHIJKL
1250025505025050100500
2
3Sum of group of 4 or more
4What I get375
5Should be150(i.e. sum of D1:G1)
6
Sheet1
 
Upvote 0
Let's assume for illustration purposes that you want to sum any/all groups of 3 or more numbers larger than 0, and that A2:L2 contains the following data...

Code: 
25	75	50	0	25	50	0	0	25	75	25	50

As I understand it, there are 2 groups of numbers containing 3 or more numbers.

Code: 
First group -----> 25, 75, and 50 

Second group -----> 25, 75, 25, and  50

...and the formula should return 325. Is this correct? If so, try the following...

Code: 
1) Select A2

2) Insert > Name > Define

Name:  Array1

Refers to:

=COUNTIF(OFFSET($A2:$L2,,,,COLUMN($A2:$L2)-COLUMN($A2)+1),0)

Click Add

Name:  Array2

Refers to:

=IF($A2:$L2<>0,MMULT(COLUMN($A2:$L2)^0,(IF($A2:$L2<>0,Array1)=TRANSPOSE(IF($A2:$L2<>0,Array1)))+0))

Click Add

Name:  Array3

Refers to:

=IF($A2:$L2<>0,MMULT($A2:$L2,(IF($A2:$L2<>0,Array1)=TRANSPOSE(IF($A2:$L2<>0,Array1)))+0))

Click Ok

Then try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

Code: 
=SUM(IF($A$2:$L$2<>0,IF(Array2>=3,Array3/Array2)))

Note that if the rows below A2:L2 contain other data, and each row needs to be evaluated in the same manner, the formula can be entered in any cell in Row 2 and copied down.

Hope this helps!
 
Upvote 0
Hi Domenic,

Thank you very much for this.... I knew you would be one of the very few that could answer this...and so I was hoping you'd see the thread.

...and I knew that if you came along, somehow the MMULT and TRANSPOSE functions would be involved. :)

...I'm kinda' glad it was this complex... I was getting nervous when I couldn't figure it out..so I knew there were some neat tricks to be had. ;)

Well, as I mentioned earlier, I was helping someone out with this and I will definitely send them here with a link... I won't take credit for your artwork. :)

Thanks again Domenic. Much obliged.
 
Upvote 0
Hi NBVC,

You're very welcome! And thanks for the kind words! I have to admit that it took me several attempts to come up with a solution. I was beginning to think that a single cell formula solution was not possible, but I've learned never to say "never". :) It seems that there's always someone with some innovative way of coming up with a solution.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Group number of purchased SKUs by counting customer
2
Replies
16
Views
292
shaowu459
shaowu459
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
177
dilshod_k
D
E
  • Question
Adding a custom message.
Replies
1
Views
458
Micron
M
A
  • Solved
STDEV.S/IF Formula Explanation
Replies
2
Views
219
Arts
A
E
  • Question
VBA for Web Scraping help
Replies
0
Views
618
epactheactor
E

Forum statistics

Threads
1,215,945
Messages
6,127,844
Members
449,411
Latest member
adunn_23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top