Answered by:
Sum values by month, if result is 0, display last sum

Hello,
I have a sheet with the following data: 3 columns with months (JanDec) and a "Value" column for each of those 3. Also, a column "Main list" with months (JanDec) and column "Total" next to it.
What I need to do for each month from "Main list" is find the sum of the values for that month from each of the 3 columns and have it displayed in "Total" for that particular month. Something like accumulation, however, what I need is that if the sum of those 3 values is >0, display it in the main list. But, if it is 0, show the last sum whose result was >0.
Here's a screenshot of how the result sheet should look like for better understanding: https://i.imgur.com/Gzc3Hi5.png
Many thanks in advance.
 Edited by Stoyian0v Friday, November 10, 2017 9:21 AM
Question
Answers
All replies

Hi,
I'm not understand how the 3000 result is calculated:
Please provide more information about your requirement and I'm glad to help you.
Regards,
Emi Zhang
Please remember to mark the replies as answers if they helped.
If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com. 
Let me try to explain:
1. For January  March the sum is 1000+0+0=1000, so 1000 is the Total for each of these months  here all is ok.
2. For April, the sum is 0+0+0=0  in this case I need the Total to show the previous result that is >0 (in this case 1000) instead.
3. Then for May  July, the sum is 0+2000+0=2000  so I need it to accumulate with the Total for the previous month (which is 1000 as I said above) and the end result should be 3000.
4. Then for August  December, their sum is again 0+0+0=0  so I need it to show the previous result that is >0, in this case this is 3000. Same logic as in point 2 of this explanation.
Thanks for your time.


Thank you for your reply.
Well, that's exactly what I'm asking, so far I've tried multiple ifs, conditions with =0/>0 and <>the sum in the row, indexing/matching the first positive value above and so on. But somewhere I'm doing it wrong. So my question is, how to make the connections/logic so that the result table looks like the one I showed above. The actual formula is the easier part :)
 Edited by Stoyian0v Monday, November 13, 2017 6:03 PM




If this is what you mean by "define"  "previous month" means the previous calculated total (shown in the Main list), i.e. the cell value above the cell I'm currently looking at (in the Main list). I'm afraid I can't make it any more clear than that.
 Edited by Stoyian0v Tuesday, November 14, 2017 4:15 PM

It's not entirely clear why the sums are what they are.
You seem to want the summary list to be a total of the three detail lists, and to incorporate some kind of running sum as well.
Why is each value only counted once?
If there is more than one value in a column, how is it possible to tell whether they count once or more than once?
If the value in a column is zero, will its actual value always be the first nonzero value in the cells above it in its column?
If this is the case, why is April zero?
Sorry to be so dense.
Love,
e. 
You seem to want the summary list to be a total of the three detail lists, and to incorporate some kind of running sum as well.
Correct.
Why is each value only counted once?
Because I need only "new" values to get added to the total.
If there is more than one value in a column, how is it possible to tell whether they count once or more than once?
If it's new, it gets added, if it isn't, it gets missed.
If the value in a column is zero, will its actual value always be the first nonzero value in the cells above it in its column?
Yes.
If this is the case, why is April zero?
It's not, April is 1000, the first positive value above it (we're talking about the Main list). It's 0 in the 3 other lists because this is the data I have for April.
I missed to say that the first value (Jan) will always be positive. Considering this, I managed to solve it  the Jan total is simply the sum of the 3 other columns, then from Feb and all the way down, the formula is =IF(AND(F3+I3+L3>0;B6>=0;F3+I3+L3<>F2+I2+L2;F3+I3+L3<>LOOKUP(2;1/(B3:B6>0);B3:B6));SUM(F3;I3;L3;LOOKUP(2;1/(B3:B6>0);B3:B6));IF(AND(F3+I3+L3=0;B6=0);LOOKUP(2;1/(B3:B6>0);B3:B6);IF(B6>0;B6;B6)))
It's a bit messy and can probably be optimized, but so far does the job.
Thanks all for your input!

I missed to say that the first value (Jan) will always be positive. Considering this, I managed to solve it  the Jan total is simply the sum of the 3 other columns, then from Feb and all the way down, the formula is =IF(AND(F3+I3+L3>0;B6>=0;F3+I3+L3<>F2+I2+L2;F3+I3+L3<>LOOKUP(2;1/(B3:B6>0);B3:B6));SUM(F3;I3;L3;LOOKUP(2;1/(B3:B6>0);B3:B6));IF(AND(F3+I3+L3=0;B6=0);LOOKUP(2;1/(B3:B6>0);B3:B6);IF(B6>0;B6;B6)))
It's a bit messy and can probably be optimized, but so far does the job.
Thanks all for your input!
Shall you learn VBA?
Regards,
Ashidacchi

If this is the case, why is April zero?
Er, yes, April is absolutely zero.
Unfortunately given the data as is you can't abstract a general case.
(I hate it when people tell me "you can't get there from here" too.)
Although you could code a specific case which will work only for this exact set of data. (As from the following post it seems you have done.)
(Which would suggest to me that you're answering a homework question rather than trying to solve any other kind of problem but that's none of my business.)
1. How do you qualify "different"? That is, how would you distinguish between the value in March being the same value as in February, or a "different" value that happens to be the same?
2. How do you know that "zero" means "not different" rather than "zero". That is, what is it about April that says it should be the same as March and not actually zero?
3. The data as you present it suggests that you have independent months. As you describe your problem, though, it seems that what you have are quarters. That is, the values for January, February and March are not separate but the same value (for the quarter) reported three times. This would make the problem much simpler, almost solvable.
Good luck,
e.