PDA

View Full Version : [SOLVED] summing a column with blank cells and replacing the blanks with a value in the sum



spittingfire
06-28-2017, 03:44 PM
Hi All,

I have what I think is an odd request.

I have the attached excel sheet and in column A (Range A1:A10) have values of either 8 or a blank. If I do a sum of A1:A10 I will get 56 however I want the sum to calculate if a cell is blank then add the value of 8. In this case the expected solution for range A1:A10 should be 80.

I'm not sure how to go about this or if it's even possible.

I'm able to do it with a helper cell but wondering if I can do it in just one formula without the use of a helper cell.

Any help will be appreciated.

Thanks

YasserKhalil
06-28-2017, 03:53 PM
Hello
May be

=SUM(SUMIF(A1:A10,8,A1:A10),COUNTIF(A1:A10,"")*8)

spittingfire
06-28-2017, 04:57 PM
Thanks YasserKhalil, much appreaciated. I have a follow up question though. If my values are instead in hh:mm:ss how will I write the formula?

mdmackillop
06-28-2017, 06:01 PM
=SUM(SUMIF(A1:A10,TIMEVALUE("08:00:00"),A1:A10),COUNTIF(A1:A10,"")*8/24)

Use custom number format [h]:mm:ss to display result

spittingfire
06-30-2017, 06:17 AM
Thanks mdmackillop for the help. Much appreciated.