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

2 comments:

  1. Looks great. how do you learn and get better at this. any course :-)?

    ReplyDelete
  2. Hi Anand:

    In my case, it is interest in spreadsheet based applications and applying the learning to practical problems -- I am an engineer (Civil/Structural Engineer) by training and I work in the designing and building of energy efficient, primarily ICF (Insulating Concrete Form), buildings.
    You may want to look at my website
    www.energyefficientbuild.com
    for some of my projects. I have not updated my website recently but there are plenty of projects depicted in there.

    Cheers!
    Yogi Anand

    ReplyDelete