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
Looks great. how do you learn and get better at this. any course :-)?
ReplyDeleteHi Anand:
ReplyDeleteIn 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