Numbers resulting from IF formulas not being captured in column sum

%3CLINGO-SUB%20id%3D%22lingo-sub-358263%22%20slang%3D%22en-US%22%3ENumbers%20resulting%20from%20IF%20formulas%20not%20being%20captured%20in%20column%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358263%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20anyone%20help%20with%20my%20excel%20problem%3F%20I%20have%20created%20an%20accounting%20spreadsheet%20in%20Excel%20with%20If%20formulas%20to%20pre-populate%20future%20date%20cells.%20However%2C%20when%20the%20numbers%20show%20up%20in%20the%20cells%2C%20they%20are%20not%20being%20captured%20in%20the%20total%20at%20the%20bottom%20of%20the%20column.%20The%20cells%20are%20formatted%20to%20'accounting'%20so%20it%20is%20not%20a%20text%20format%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-358263%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359895%22%20slang%3D%22en-US%22%3ERe%3A%20Numbers%20resulting%20from%20IF%20formulas%20not%20being%20captured%20in%20column%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359895%22%20slang%3D%22en-US%22%3E%3CP%3EUnfortunately%2C%20that%20does%20not%20seem%20to%20work%20either.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359772%22%20slang%3D%22en-US%22%3ERe%3A%20Numbers%20resulting%20from%20IF%20formulas%20not%20being%20captured%20in%20column%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359772%22%20slang%3D%22en-US%22%3EEnsure%20that%20all%20entries%20in%20L14%3AL48%20are%20numbers.%20Is%20your%20sum%20formula%20located%20in%20L49%3F%20Do%20you%20mean%20some%20cells%20in%20L14%3AL48%20are%20not%20included%20in%20the%20sum%3F%20Try%20this%20formula%3A%3CBR%20%2F%3E%3DSUMPRODUCT(%E2%80%94L14%3AL48)%3CBR%20%2F%3EThe%20double%20unary%20(%E2%80%94)%20before%20the%20range%20ensures%20that%20all%20entries%20therein%20are%20converted%20to%20numbers.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-359767%22%20slang%3D%22en-US%22%3ERe%3A%20Numbers%20resulting%20from%20IF%20formulas%20not%20being%20captured%20in%20column%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-359767%22%20slang%3D%22en-US%22%3E%3CP%3EI%20used%20a%20basic%20sum%20formula%3C%2FP%3E%3CP%3Ei.e.%26nbsp%3B%20%26nbsp%3B%3DSUM(L14%3AL48)%3C%2FP%3E%3CP%3EMy%20fear%20is%20that%20the%20'customized%20formula%20cell'%20is%20not%20being%20read%20as%20a%20number%20and%20therefore%20not%20being%20included%20in%20the%20column%20total.%20Which%20to%20me%20seems%20illogical%20in%20a%20spreadsheet%20if%20I%20am%20trying%20to%20pull%20in%20dollar%20amounts%20for%20specific%20weeks%20of%20the%20month.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358340%22%20slang%3D%22en-US%22%3ERe%3A%20Numbers%20resulting%20from%20IF%20formulas%20not%20being%20captured%20in%20column%20sum%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358340%22%20slang%3D%22en-US%22%3EWhat%E2%80%99s%20your%20formula%20to%20calculate%20the%20sum%3F%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Can anyone help with my excel problem? I have created an accounting spreadsheet in Excel with If formulas to pre-populate future date cells. However, when the numbers show up in the cells, they are not being captured in the total at the bottom of the column. The cells are formatted to 'accounting' so it is not a text format problem.

4 Replies
Highlighted
What’s your formula to calculate the sum?
Highlighted

I used a basic sum formula

i.e.   =SUM(L14:L48)

My fear is that the 'customized formula cell' is not being read as a number and therefore not being included in the column total. Which to me seems illogical in a spreadsheet if I am trying to pull in dollar amounts for specific weeks of the month.

Highlighted
Ensure that all entries in L14:L48 are numbers. Is your sum formula located in L49? Do you mean some cells in L14:L48 are not included in the sum? Try this formula:
=SUMPRODUCT(—L14:L48)
The double unary (—) before the range ensures that all entries therein are converted to numbers.
Highlighted

Unfortunately, that does not seem to work either.