How to produce multiple sums efficiently, grouping ranges of cells with positive values (contiguous and non-contiguous)

[TonyJones]

Hello

I was wondering if somebody could point me in the right direction please?

I want to produce multiple sums in the most efficient way using the following criteria:

• IFsingle cell has positive value then sum = to that value (all cells with a positive value are highlighted in light red using conditional formatting)
• IFadjacent cell(s) have positive values, group all cells together as one sum (for example, A10 and A11 become 1.41%)
• Sum all values per column on this basis
• Sum Max value from each column

So in this case, there would be 6 sums for column A returning the following values: 0.30%, 0.68%, 1.41%, 2.17%, 3.32%, 1.24%

I imagine that I would need a helper column somewhere along the way and that I may be using SUMIF? although I have about 1700 values per column so calculating a sum per positive value would take a lot of time!

Thanks in advance

P.S If you have any suggested tags or title tags for this post, so that it can help others in the future, just let me know.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is it something like this you are after?

20 03 02.xlsm
	A	B
1	Data
2	0.00%
3	0.30%	0.30%
4	0.00%
5	0.00%
6	0.00%
7	0.68%	0.68%
8	0.00%
9	0.00%
10	0.00%
11	1.19%
12	0.22%	1.41%
13	0.00%
14	0.85%
15	0.77%
16	0.55%
17	0.85%	3.02%
18
Sums

Cell Formulas
Range	Formula
B2:B17	B2	=IF(AND(A2>0,A3=0),SUM(A$1:A2)-SUM(B$1:B1),"")

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[TonyJones]

Hi Peter

That's exactly what I'm looking for, thank you!

I see that we are looking for a value greater than 0.

Please can you explain the logic to me a little more?

I will investigate XL2BB also. Thanks

 

[Peter_SSs]

That's exactly what I'm looking for, thank you!

You're welcome.

Please can you explain the logic to me a little more?

Take the formula in B12
=IF(AND(A12>0,A13=0),SUM(A$1:A12)-SUM(B$1:B11),"")

=IF(AND(A12>0,A13=0), ...
This checks that the cell in column A is positive and the cell below is zero. That is, it identifies if the row is the last row of a series of non-zero cells. This is true for row 12

SUM(A$1:A12)-SUM(B$1:B11)
This sums everything in column A down to and including row 12 (this sum will include 0.30% from A3 and 0.68% from A7)
and then subtracts all the values in column B above row 12. This will remove the sums of any column A cells already accounted for, leaving, in this case, the sum of A11:A12 only.