CODEWITHSUNDEEP
google-sheetsgoogle-docsformulas
dwcouch
dwcouch

Reputation: 49

Google Docs - SUMIF - SUM range if single cell contains a string

I'd like to quickly include or exclude an entire range of values in a SUM. Presently I'm SUMing select cells for a grand total: [E19] =SUM(E13,E20,E30,E45,E55,E70,E80)

These are in turn SUMs of selected ranges: ... [E30] =SUM(E31:E44), [E55] =SUM(E56:E69), ...etc.

One of these ranges I would like to toggle it's inclusion in the Grand Total.

It seemed the best way to do it was this: [E45] =SUMIF(D45,"☑",E46:E54)

In short, in cell E45 I'd like to SUM E46 to E54 only if D45 contains a ☑.

However Google Doc's SUMIF seems to only work with matched ranges: =SUMIF(D46:D54,"☑",E46:E54)

Is there a way to SUM a range only if a specific value exists in a single cell?

Upvotes: 1

Views: 2562

Answers (2)

dwcouch
dwcouch

Reputation: 49

After working through the logic to share the issue I wound up identifying a solution. Rather than trying to force SUMIF to check a single cell against a range. I just nested the 1:1 SUMIF inside my 'Grand SUM': =SUM(E13,E20,E30,SUMIF(D45,"☑",E45),E55,E70,E80).

Upvotes: 0

AJPerez
AJPerez

Reputation: 3595

You're right about SUMIF, it allows you to sum values from a range, which meet a certain criteria (on another range of the same length). For example, if you had two columns called "status" and "price", you could use it to sum all the prices for a given status.

What you're trying to do can be done, instead, with the use of the IF function:

=IF(D45="☑";SUM(E46:E54);0)

If the condition specified in the first argument is true, it will return the second argument, that is, the sum. Otherwise, it will return the third argument, 0.

Upvotes: 1

Related Questions