Need some help with a formula

Question

I need a column to allow me to Add multiple dates in 1 cell, then total the amount of dates in another cell. See the attached file for reference on what I need.

Example Dates:

in cell B: 1, 5, 12, 15, 17

Total of dates in cell C: 5

hansvogelaar · Answer

stephbuch11&nbsp;
Format C8 as General instead of as Text, and enter the following formula in C8:
&nbsp;
=LEN(B8)-LEN(SUBSTITUTE(B8,",",""))+1

mtarler · Answer

stephbuch11&nbsp;When you copied down you somehow kept the first cell reference as C8.&nbsp; Did you copy down or paste the text in each cell and manually update?&nbsp; I corrected the problem in the attached and added the empty condition to =0.

mtarler · Answer

stephbuch11&nbsp; You're very welcome.&nbsp; Glad it works.&nbsp; Make sure to give&nbsp;HansVogelaar&nbsp; some Likes for getting you the original formula.FYI for future.&nbsp; You can copy a formula down (either: a) copy, highlight the whole range and then paste or b) put the cursor on the dot in the bottom right corner and either double-click or drag it over the range or c) highlight the starting cell and the rest of the range and 'Fill Down' (or Right if it is a row)then you won't have manual typing errors as long as you set up the formula correctly to start.&nbsp; If you do more formulas you will want to learn about how to use a '$' to prevent excel from incrementing those references when you copy/fill down (it makes the reference absolute instead of relative).&nbsp; Excel can do great things, but I don't want to overwhelm you either.Best of luck and love the company name/logo 🙂

stephbuch11 · Answer

Sweet! That totally worked thank you!

stephbuch11 · Answer

Does it always have to have the 1? sometimes the final will be zero, if they didn't use that particular service. Or can I just put 0 as needed?

Forum Discussion

Need some help with a formula

9 Replies

Resources