Excel Countif and sum functions not working

Question

I have a spreadsheet of reservations for a room. It lists who is responsible for that reservation.  At the top, I have a summary where it will count each name and give a total (ie - "=countif I:I,A2" where "A2" is the persons name).  This works fine.  I then try to sum this column of numbers. The function arguments box while I build it gives the "formula result= 37" but when I hit OK, the cell says "0" and I can't run my other formula based on that total.  What is going on???? How do I fix it????

Answer 1

The values in column D are probably text.  Confirm with formulas like =ISTEXT(D2).  A visual inspection can be misleading, and the cell format does not matter.

A quick-and-dirty solution is:  =SUMPRODUCT(--D2:D5).  But other references to column D might cause problems.

It would be better to replace the text values with numeric values.  Often, you can use Text To Columns.  Select D2:D5, click Data > Text To Columns, then press Finish.

FYI, the Function Wizard (fx) works because it is smarter (albeit misleading) in interpreting the cell values.

Answer 2

The =istext(d2) returns a "0".

Using the =sumproduct(d2:d5) also still returns a "0"

Not sure what you mean by the text to columns.  These are formula results in a column already.

Answer 3

Hi,

See if my article here helps - https://answers.microsoft.com/en-us/msoffice/forum/all/remove-space-resembling-characters-from-a-range-of/2a13073e-5cac-49be-846d-7d02dfc9cbfe.

Answer 4

I guess I somehow didn't make myself clear.  This is not text, it was not copied from somewhere else and is not text that needs to be put in columns.

This is data created by formulas as I stated in my original post.

Answer 5

What formula do you have in cell D2?