SUMIFS - Numbers Match but returns a 0 value

[dunmore83]

Hi,

In cell G49 I have the following formula;

=IF(B49="","",-SUMIFS('MYOB-Yr-To-Date'!$E$111:$E$130,'MYOB-Yr-To-Date'!$N$111:$N$130,$B49))

In cell N111 in MYOB-Yr-To-Date I have the following formula;

=IFERROR(INDEX('Jobs-MYOB'!$L$12:$L$156,MATCH($D111,'Jobs-MYOB'!$M$12:$M$156,0)),"")

Cell B49 has no formula - it has the value of 6-2030 formatted as a number. The cell N111 is the value of 6-2030 (but has a formula) formatted as a number.

If I overwrite the formula in cell N111 with 6-2030 the SUMIFS works.

Can anyone please help ?

 

[jtakw]

Hi,

Your 6-2030 value in either B49, and/or N111 is Text, not a number.

In an unused cell, enter
=ISNUMBER(B49)
In another cell, enter
=ISNUMBER(N111)

What do you get?

NOTE: Cell formatting does Not change the underlying value of the cell's contents, if the value is Text, formatting to Numbers won't change it to numbers, it's Still Text.

 

[dunmore83]

Thanks

They both return a FALSE value however the cell formatting shows Number for both of those cells ??

 

[jtakw]

That means they're Text, not numbers, as I pointed out, cell formatting does Not change the Actual value.

Now, how Exactly are you entering 6-2030 in B49 (no formula, as you stated)?
And, how does N111 get 6-2030 from the cell formula, I know you have an INDEX/MATCH for the cell, but the Origin of that value "6-2030", where did that come from, or how is it entered?

 

[dunmore83]

Thanks

6-2030 just shows as 6-2030 in cell B49 (no formula & formatted as a Number)

Cell N111 references a column in a separate worksheet which also has 6-2030 (formatted as a number but returns false when I reference that cell with ISNUMBER

 

[jtakw]

Now, how Exactly are you entering 6-2030 in B49 (no formula, as you stated)?
And, how does N111 get 6-2030 from the cell formula, I know you have an INDEX/MATCH for the cell, but the Origin of that value "6-2030", where did that come from, or how is it entered?

You're not answering my question.

Example, if you enter 6-2030 in Any cell, which gives you June 1st of 2030, which in number format, would result in 47635.
So, again, how did you get B49 to show as "6-2030" exactly?
And also my second question above apply to same.

 

[dunmore83]

Thanks

The value in cell 6-2030 was exported from an accounting software call MYOB so maybe there is an issue with the formatting of this cell. Can we create a reference to cell B49 which will have a formula so that the SUMIFS formula finds a match ? (& then do the same to cell N111)