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 simplify, I have 3 cells. Let's call them A1, B1 and C1. These cells contain a value as I input data each month. So, row 1 would be Jan, 2 February etc.  up to December. D1 would be my result.

 

I want to Add together A1+B1+C1 and put that into D1. If however I have no data I want blank "" in D1.

 

The issue I have is the months I have no data for yet returns #value. I want it to read blank until I input data but I don't want to copy the formula each time, I want the formula to always stay in the sheet.

 

I have managed to create a formula for returning blank if I Have 1 cell blank =IF(A1="","",A1+B1+C1), but I can't see how to look at 3 cells for blank. I have tried multiple methods like =IF(A1,B1,C1="","",A1+B1+C1) etc but get error messages or #value returned in D1

 

I hope this makes sense and thank you again.

excel
Formulas and Functions

4 Replies

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 08, 2024

    danriley92 

    Another formula for 365 would be

    = BYROW(values, LAMBDA(v, IF(AND(v<>""), SUM(v), "")))

    Not only should this work on row1 values, it would repeat the calculation on all the rows in the range 'values'.

    • 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 how IF works:

        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