Tuesday, May 31, 2011

yogi_Sum Up Numbers In A Column Corresponding To Non-Blank Rows In Another Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
anandrajk said:
Use the SUM IF function to add only those cells corresponding to a non-empty cell in the specified range.
for eg. in column C i have values ( numbers, text and empty cells). I want to add all the cells in column D that have non-empty cells in column C.
I know how to search for blank cells and it works. I use SUMIF(C3:C10,"",D3:D10) . This sums all values in column D for which column C is blank. I want to do the opposite.
would appreciate any help
In the following ...
I have a solution using SUMIF function in Sheet1
a solution using the FILTER function in Sheet2
Sheet3 has a solution using the QUERY function (by first convering all entries in range C:D to TEXT)
Sheet4 has another solution using the QUERY function and aggregating column D
by the way in this case solutions using the SUMIF and the FILTER function are so straight forward
I have included the solution using the QUERY function out of interest especially since column C is mixed TEXT, NUMBERS, and BLANKS