+ Reply to Thread
Results 1 to 6 of 6

Summing cells depending on the value of the cells above.

  1. #1
    Registered User
    Join Date
    08-28-2013
    Location
    Bristol
    MS-Off Ver
    Excel 2007
    Posts
    26

    Summing cells depending on the value of the cells above.

    Hi,

    I am building a spreadsheet which lets me track the volume of certain metals dug up from the ground. Before a dig there is an expected volume for certain metals. Sometimes other metals appear when we begin digging. I want to be able to track the volumes of metals we dig up but I want to be able to sum them automatically. One column of the summation of the metals we expected and one summation of the metals we did not.

    I have attached the spreadsheet I am using.

    I imagine it is the SUMIF function I want but not sure how to code it.

    In laymens terms I want in one column:
    SUMIF (Only include in the sum if (In the top line your column has a value (>0??), if not you wont be included)
    SUMIF (Only include in the sum if the top value of your column is 0)

    Hope this makes sense.

    Many thanks,

    Will
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,007

    Re: Summing cells depending on the value of the cells above.

    K5 for expected as per your initial formula

    L5 for unexpected:

    =SUMIF(F20:J20,0,F21:J21)

    Does it work?

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: Summing cells depending on the value of the cells above.

    I think these will do it:
    Please Login or Register  to view this content.
    Then pull down.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Summing cells depending on the value of the cells above.

    I wasn't sure what you wanted... whether tou wanted a cumulative total. or what. So for EXPECTED (K21 drag down) I used:

    =SUMPRODUCT(($F$20:$J$20>0)*$F$21:$J21)

    for UNexpected, L21, drag down, I used:

    =SUMPRODUCT(($F$20:$J$20=0)*$F$21:$J21)
    Attached Files Attached Files
    Glenn



  5. #5
    Registered User
    Join Date
    08-28-2013
    Location
    Bristol
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Summing cells depending on the value of the cells above.

    Hi Glenn,

    Thanks very much for getting back to me so quick. I don't want a cumulative value I just want what you did but for the individual rows. How do I edit your formula to achieve this.

    Kind regards.

    Will

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Summing cells depending on the value of the cells above.

    Expected:
    =SUMPRODUCT(($F$20:$J$20>0)*$F21:$J21)

    Unexpected:
    =SUMPRODUCT(($F$20:$J$20=0)*$F21:$J21)

    is that it?

+ 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. summing up contents on ceratin cells depending on selection criter
    By KL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2005, 03:05 PM
  2. summing up contents on ceratin cells depending on selection criter
    By KL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 11:05 AM
  3. summing up contents on ceratin cells depending on selection criter
    By KL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 07:05 AM
  4. [SOLVED] summing up contents on ceratin cells depending on selection criter
    By Charles in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  5. [SOLVED] summing up contents on ceratin cells depending on selection criter
    By Charles in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. summing up contents on ceratin cells depending on selection criter
    By Charles in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. summing up contents on ceratin cells depending on selection criter
    By Charles in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. summing up contents on ceratin cells depending on selection criter
    By Charles in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Tags for this Thread

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