SUMIF returning 0 (formatting?)

[Yakov on Excel]

Hey guys,

I'm having trouble with a SUMIF formula only returning 0

I've looked around already for an answer, but I can't seem to find one that fixes my problem.

I have a the SUMIF formula set up in about 200 rows, and I have one instance of it working properly in the whole list. When I click and drag the working formula it still won't calculate anything but a 0 in the other cells.

HOWEVER, when I click and drag the cell that the working formula is looking to (range) it will start to work in the other formulas.

This made me believe that it was a formatting discrepancy (that the values in the range column were did not have valid formatting or something) so I used the formatting brush to apply identical formatting from the working cell to the other cells, but still no dice. This is REALLY wierding me out.

Any ideas?

[JohnTopley]

Without having data i.e. Excel file, it is impossible to determine the problem.

Post a small sample containing the problem.

[Yakov on Excel]

Without having data i.e. Excel file, it is impossible to determine the problem.

Post a small sample containing the problem.

K- here's what's going on

[JohnTopley]

The problem lies with difference in formatting between data in "Dashboard" and "LotsofData" although format suggests they both are number.

Copying data from Dashboard to corresponding parts in "LotsofData" solved the problem.

Perhaps parts should always be TEXT to avoid number format problems; in some cases it thinks the Part data is a date

[Yakov on Excel]

Ok, now is there a way that I don't have to manually paste over the top of 450 values?

[Yakov on Excel]

Hey John, I feel really silly now....
The data had been imported from a PDF, and the character code for the "-" was different than a regular typed "-" even though they appeared identical even at a high zoom. A simple find and replace fixed the problem.

Thank you very much for your timely response to my post!

[FlameRetired]

The problem lies with difference in formatting between data in "Dashboard" and "LotsofData" although format suggests they both are number.

Copying data from Dashboard to corresponding parts in "LotsofData" solved the problem.

Perhaps parts should always be TEXT to avoid number format problems; in some cases it thinks the Part data is a date

This formula in both sheets reveals a problem.

Please Login or Register  to view this content.

Click in the formula bar in each sheet and press the F9 function key and see these arrays.

In Lotsofdata
{49;48;45;49;55}

In Dashboard
{49;49;63;53;53}

The third character from the left should be the same in both (the "dash"). They aren't. They are CODE 45 and 63 (third character from the left).

The two sets will never match. Do find and replace and your formula should work.

Opps! Posted late.

[Yakov on Excel]

Yeah, I just saw that, it's weird though, I thought that character code 63 was the code for "?"