SUM IF Error

Hello!

Have used this forum many times and it has been so useful, but now I'm really stuck and I can't find any answers on here so thought I would ask my first question!

I have attached a spreadheet that should sum the value in column G based on the value of column A* however it seems to think that "1-02" & "2-14" are the same number and sums it all?!?!

Im guessing that this is a date thing maybe?? But does anybody have an idea of how i can get around this as I cant change the code and I need a seperate sum of each code?

Many Thanks in advance for all your help.

Sam



SUMIF Error.xlsx

Hi, I think you are right in that sumif is looking at them as dates. I don't think there is a way to force sumif to stop doing this though so you may be better with a formula like:
=SUM(IF(F1:F2=A1,G1:G2,0))
it's an array formula so you need to confirm with ctrl+shift+enter.

That has returned the correct results.

Thank you so much!

Originally Posted by XOR LX

Welcome to the forum and wow! What an interesting feature you've unearthed!

I have no idea why SUMIF can't cope with interpreting the string as text, but will investigate. In the meantime, there is also this (non-array) alternative:

=SUMPRODUCT(($F1:$F2=$A2)*$G1:$G2)

Regards

It's been driving me crazy, but your formula seems to work prefectly. Thank You!

Are these meant to be dates?

Windy

No, they are account codes...

Sam

The problem is presumably, that
=datevalue("1-02")
and
=datevalue("2-14")
both return 41671 and presumably that is how sumif is comparing them.

Was just playing about, and thought it was interesting that using vba:

Please Login or Register  to view this content.

returns 100...

Although I hugely appreciate all your help, (Even though it gave the correct reult) I could not get my head around the sum product formula and how it calculated, I also had to do a vlookup for another piece of info as well and wasn't sure how to add that in! So I have chosen to solve this issue a slightly different way. I realise this option may not be applicable for all but it seems to be working for me so far!

I have changed the data I am running this formula on. So now instead of being '1-02' I have added a prefix of "/" to give "/1-02". I can now run my formula based on this new field:

Formula:

Please Login or Register  to view this content.

I realise this may not be the most efficient solution but it gives me the correct result

Many Thanks,

Sam

As I understand it, SUMIF/COUNTIF etc. evaluates the string expression that denotes the condition, trying to make sense of it as best it can. The results above, though unexpected for the unwary, are logically correct. Rory's approach is how you can get round it.

Pete

Originally Posted by sam01722

I realise this may not be the most efficient solution but it gives me the correct result

Hi Sam,

if you changed the codes to use an underscore instead of the hyphen then it should work okay, and you wouldn't need the prefix.

Hope this helps.

Pete

Originally Posted by Pete_UK

Hi Sam,

if you changed the codes to use an underscore instead of the hyphen then it should work okay, and you wouldn't need the prefix.

Hope this helps.

Pete

Unfortunatley the ERP system I am querying the data from only allows for hyphens and not underscores to form the code. But that is still very interesting to know.

Sam

Once you have imported the data you could use Find & Replace to change - to _ for your column containing the codes, or you could use a helper column to do that with the SUBSTITUTE function and use the helper column as the reference in your formulae, so you still have the original codes if you need to export anything back.

Pete

Originally Posted by Pete_UK

Once you have imported the data you could use Find & Replace to change - to _ for your column containing the codes, or you could use a helper column to do that with the SUBSTITUTE function and use the helper column as the reference in your formulae, so you still have the original codes if you need to export anything back.

Pete

Thats interesting, I never knew about the substitute formula. I have just given it a try and it seems to only work for one "-". For example, The code "1-01-000" changes to "1_01-000" ....?

It worked for me - I put this value in A1:

1-01-000

and this formula in B1:

=SUBSTITUTE(A1,"-","_")

and this was the result:

1_01_000

Hope this helps.

Pete

Originally Posted by Pete_UK

It worked for me - I put this value in A1:

1-01-000

and this formula in B1:

=SUBSTITUTE(A1,"-","_")

and this was the result:

1_01_000

Hope this helps.

Pete

Ahhhhhh yes it does indeed work :/ Not sure what ! did last time haha!!!!! Thanks