Get the sum of a column but only if the row isn't checked?

Question

Level 1

5 points

Get the sum of a column but only if the row isn't checked?

Right, so I have a list of upcoming purchases. Each row has a checkbox, a description, a min-price and a max-price (based on estimates I enter manually). In the footer-row I have a sum for the min and max column. However, I'd prefer to only include the sum of items where their corresponding check box is not checked.

So, in other words, when a check box is checked, I want that min and max not to be included in the corresponding sum.

However, I have no idea how to do an if statement that tests to see if a box in the same row is checked. the checkbox is in column A and the sums are in C and D, respectively.

iMac, Mac OS X (10.6.7), Intel Core i7 2.93Ghz / 12GB RAM / 1TB HDD / ATI 5750HD @ 1GB VR

Posted on May 16, 2011 6:40 PM

Reply

Answer 1

Best reply

Badunit

Level 7

25,586 points

May 17, 2011 7:34 AM in response to Doug_S

You are looking for the SUMIF function

=SUMIF(A,"False",C)

Reply

Answer 2

May 17, 2011 7:43 AM in response to Badunit

It's a good solution. I would add that the quotes aren't necessary. If you don't use the quotes, the case will change to all caps to indicate recognition of the boolean state.

Jerry

Reply

Answer 3

Doug_S Author

Level 1

5 points

May 17, 2011 8:53 AM in response to Badunit

That did it! Thanks to both of you!

Reply

Answer 4

Apr 28, 2016 9:47 AM in response to Badunit

Hello Badunit,

I tried what you mentioned in this thread.

I got error message at each attempt.

Column A contains dates, B numbers.

I filter by A searching for certain dates, and then add the B's accordingly. The result is in a B cell foot row. Of course I only want to add the visible ones.

SUMIF contains a test i don't know how to express: I want to add B if the corresponding A contains a date, or is not empty for example. Unable to write this test properly.

If you have any idea, i would be interested.

Thank you for your time.

Lionnel

Reply

Answer 5

Apr 28, 2016 9:49 AM in response to lionneldeparis

you are piggy backing on an old three loosely related to your question. You should post a new question.

Reply

Answer 6

Barry

Level 9

59,861 points

Apr 28, 2016 1:51 PM in response to lionneldeparis

Hi lionnel,

"I want to add B if the corresponding A contains a date, or is not empty for example. Unable to write this test properly."

Use an auxiliary column, which may be hidden (I've used E in the example below), containing a formula that measures the entry in column A in characters. Any cell that "contains a date, or is not empty" will produce a result greater than 0, an will be included by the SUMIF formula shown below the table.

Formula in E2, and filled down: =LEN(A)

Regards,

Barry

Reply

Answer 7

SGIII

Level 8

35,258 points

Apr 28, 2016 4:41 PM in response to lionneldeparis

After a bit of experimenting, if found this also works:

=SUMIFS(B,A,"<>"&"")

If the table is filtered this will still include rows hidden by the filter. To exclude them you could add another column-condition pair to SUMIFS that has the same effect as the filter.

SG

Reply

Answer 8

Barry

Level 9

59,861 points

Apr 28, 2016 6:03 PM in response to SGIII

"After a bit of experimenting, if found this also works:"

Well done! I got fixated on LEN, and wasn't able to write it in as a condition. 😕

Regards,

Barry

Reply