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

    Question

  • Hello,

    I have a sheet with the following data: 3 columns with months (Jan-Dec) and a "Value" column for each of those 3. Also, a column "Main list" with months (Jan-Dec) 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
    Thursday, November 09, 2017 6:25 PM

Answers

  • Hi Stoyian0v,

    How to calculate when (the sum of the month = 0) and (the previous value = 0)? 
    You need to provide how to calculate in all cases.
        

    Regards,

    Ashidacchi

    • Marked as answer by Stoyian0v Wednesday, November 15, 2017 8:57 AM
    Saturday, November 11, 2017 12:30 AM

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.

    Friday, November 10, 2017 9:44 AM
    Moderator
  • 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.

    Friday, November 10, 2017 11:59 AM
  • Hi Stoyian0v,

    How to calculate when (the sum of the month = 0) and (the previous value = 0)? 
    You need to provide how to calculate in all cases.
        

    Regards,

    Ashidacchi

    • Marked as answer by Stoyian0v Wednesday, November 15, 2017 8:57 AM
    Saturday, November 11, 2017 12:30 AM
  • 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
    Monday, November 13, 2017 6:00 PM
  • Please fill all of cells "how to calculate" in order for us to sum.


    Ashidacchi

    Monday, November 13, 2017 6:06 PM
  • Monday, November 13, 2017 6:29 PM
  • Thanks.

    I suppose you need to define "previous month" more specific. 


    Ashidacchi

    Monday, November 13, 2017 7:45 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
    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 non-zero 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.

    Tuesday, November 14, 2017 4:43 PM
  • 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 non-zero 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!

    Wednesday, November 15, 2017 8:56 AM
  • 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!

    Yes, I agree with you. It's very messy, hard to understand at a glance, for me.
    Shall you learn VBA?

    Regards,


    Ashidacchi

    Wednesday, November 15, 2017 10:49 AM
  • 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.

    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.

    Wednesday, November 15, 2017 12:55 PM