# Summing cells depending on the value of the cells above.

1. ## 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

2. ## 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. ## 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.

4. ## 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)

5. ## 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. ## 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?

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

#### 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