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  Register To Reply

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?  Register To Reply

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

I think these will do it: Then pull down.  Register To Reply

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)  Register To Reply

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  Register To Reply

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?  Register To Reply

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