Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I am trying to get a sum of column values in a Google Spreadsheet only if there is no value in the adjacent column. Consider the following example:

  A | B
 -------
 40 | 50
 20 | 20
 30 | 
 10 | 

The sum of A should return 40.

I tried to use the following function:

=SUM(FILTER(A:A, ISEMPTY(B:B)))

but all I get is: Error: Unknown function name ISEMPTY. Can anyone tell me what's wrong or how to do it properly?

share|improve this question
    
can you show with an example (input and output)? –  Siva Charan Feb 9 '12 at 17:30
add comment

2 Answers

up vote 5 down vote accepted

Replace ISEMPTY with ISBLANK

Your formula should be as follows:-

=SUM(FILTER(A:A, ISBLANK(B:B)))

Now you will get the result as 40

share|improve this answer
    
Thank you, that was it! –  Haes Feb 9 '12 at 23:02
add comment

If there is a value of 0 in the cell, it won't be flagged as blank. But you can use:

=SUMIF(B:B,0,A:A)

Or more restricted:

=SUMIF(B2:B50,0,A2:A50)

template: SUMIF(range, criteria, sum_range)

Adds the cells specified by a given criteria. Range is the range to which the criteria are to be applied. Criteria is the cell in which the search criterion is shown, or the search criterion itself. In the criteria section, you can use a question mark (?) to match a single character or an asterisk (*) to match a sequence of characters. If you'd like to find an actual question mark or asterisk, you can type a tilde (~) before the question mark (?) or asterisk (*). Sum_range is the range from which values are summed, if it has not been indicated, the values found in the Range are summed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.