+ Reply to Thread
Results 1 to 8 of 8

How To Sum Cells When Value Changes In Another Column From 0 to 1?

  1. #1
    Registered User
    Join Date
    08-23-2011
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    57

    How To Sum Cells When Value Changes In Another Column From 0 to 1?

    Hi All,

    I have a question I was hoping someone could please help me out with!

    I have three consecutive columns. The first column has numbers that can repeat, but are always sorted from low to high. The second column will always have a 0 or 1 in it (formatted as a Yes or No). The third colmum contains numbers I would like to sum.

    The conditions are: For every individual number in the first column, I want to: (i) sum all the numbers in the third column which have 0 (No) next to them in the second column, (ii) stop the sum in the row before the number in the second column switches to a 1 (Yes), (iii) restart a new sum every time it shifts back to 0 (No) again, and (iv) if it ends on a 0 (No) before the number in the first column changes, do one final sum of all the remaining 0s (Nos).

    I think I am most of the way there with the following formula (the only issue I am having is that it's including 1s (Yeses) in the sums): =IF(AND(L3=0,OR(L4<>L3,K4<>K3)),SUM($M$2:M3)-SUM($N$1:N2),"").

    I imagine this might get confusing, so I'll do my best to help by attaching the spreadsheet. The three columns are K, L and M, and I'm trying to write the formulas in column N.

    Thank you!!!!
    Attached Files Attached Files
    Last edited by excel-help; 08-04-2020 at 12:29 PM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: How To Sum Cells When Value Changes In Another Column From 0 to 1?

    Hi,
    where is your 0/1 column?

  3. #3
    Registered User
    Join Date
    08-23-2011
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: How To Sum Cells When Value Changes In Another Column From 0 to 1?

    The 0/1 column is in Column L!

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: How To Sum Cells When Value Changes In Another Column From 0 to 1?

    In N2 and down:
    =IF(K2=K1,"",SUMIFS(M:M,L:L,$L$2,K:K,K2))

  5. #5
    Registered User
    Join Date
    08-23-2011
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: How To Sum Cells When Value Changes In Another Column From 0 to 1?

    Thanks, but it's not quite working for me.

    For example, If you scroll to the 5s in column K, it should say 26:09 in cell N40 (as the sum M40:M56), then 00:21 in cell N58 (as the sum of N58 alone, since L57 has a 1 in it), then 00:14 in cell N79 (as the sum of M79:M80)... I noticed you put the sums at the top rather than the bottom. Either way is perfect!

  6. #6
    Registered User
    Join Date
    08-23-2011
    Location
    California
    MS-Off Ver
    Excel 365
    Posts
    57

    Re: How To Sum Cells When Value Changes In Another Column From 0 to 1?

    I finished my formula offi. I just made the first sum a sumproduct!

    It may not be elegant, but it was simple! I apologize for posting. I should've worked just a bit harder first. Formula that seems to work (testing now): =IF(AND(L40=0,OR(L41<>L40,K41<>K40)),SUMPRODUCT(($L$2:L40=0)*($M$2:M40))-SUM($N$1:N39),"")

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: How To Sum Cells When Value Changes In Another Column From 0 to 1?

    Very Nice!

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How To Sum Cells When Value Changes In Another Column From 0 to 1?

    With the formula's in the table.


    After that with a pivot table.


    See the attached file.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 12-25-2018, 02:34 AM
  2. vba code to filter blank cells in a column only if the column has blanks cells
    By meus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-28-2016, 07:05 AM
  3. [SOLVED] Simple Question: Dividing The Cells In Column C by the the Cells in Column B
    By blickers in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-03-2015, 05:18 PM
  4. Replies: 2
    Last Post: 04-17-2014, 03:30 PM
  5. [SOLVED] Auto populate cells in one column based if cells in next column are populated
    By diablo86 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-22-2013, 06:29 AM
  6. Comparing cells in a column and adding result of comparison in cells of adjacent column
    By Lionel welshboy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2013, 05:37 AM
  7. Replies: 1
    Last Post: 08-16-2006, 08:20 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1