Forum Discussion

danriley92's avatar
danriley92
Copper Contributor
Oct 08, 2024

If multiple cells are blank return blank, only add if all cells have value

Good morning all and thank you for taking the time to read my post.   I'm new to excel and trying to create a document with some values in of my monthly electricity bill (boring I know lol).   To...
excel
Formulas and Functions
Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 08, 2024

Hi danriley92 

 

in D1:

=IF( COUNT(A1:C1) < 3, "", SUM(A1:C1) )

does what you expect?

danriley92's avatar
danriley92
Copper Contributor
Oct 08, 2024
Hi Lorenzo and thanks for the rapid response!

I Adjusted to suit my sheet =IF(COUNT(E29,H29,K29)<3,"",SUM(E29,H29,K29)) and it worked perfectly.

Please could you break the formula down as I'm not sure which part does what.

Thank you
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 08, 2024

    danriley92 

    =IF(
  COUNT(E29,H29,K29) < 3, "", SUM(E29,H29,K29)
)

    The point is to understand https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2:

    IF( logical_test,
    value_to_return_if_logical_test_returns_TRUE,
    value_to_return_if_logical_test_returns_FALSE
)

     Your logical_test is:

    COUNT(E29,H29,K29) < 3

     which COUNTs the number of Numeric values in E29,H29,K29 and compare that COUNT to 3. So:

    IF( COUNT is < 3,
  THEN this returns TRUE (value_to_return_if_logical_test_returns_TRUE) ==> the double-quotes
  ELSE this returns FALSE (value_to_return_if_logical_test_returns_FALSE) ==> SUM(E29,H29,K29)
)

    Hope this helps. If topic close there's a Mark as answer link at the bottom of each reply you get here,  clicking it helps people who Search - Thanks

Resources