SUMIF shows wrong value

[psykfall123]

Hi,

Ive been facing a problem with SUMIF, it works in one sheet but it doesnt work in sheet2.
In the first sheet i use SUMIF to match against the Membernumer, then i take the value from the N colume and add it to sheet 5.
=SUMMA.OM(Medlemsregister!A2:A700;"519";Medlemsregister!N2:N700)
http://imgur.com/Ky4GW

That is working great. But when i try to do the same thing to this sheet. I want to search throght column H to search for the membernumber. If i found it i want to get the Value from column E in the same row and copy that to sheet 5.
=SUMMA.OM(Blad5!H2:H700;"=38";Blad5!E2:E3)
http://imgur.com/rOySR

When it should be the value 2 i get 4, and when it should be the value 1 i get 5. Strange logic. Please help.

Im from sweden and thats because my grammar is bad.

[Pepe Le Mokko]

Can't open you examples - Why do you comapre to a string ? "=38". Is =SUMMA.OM(Blad5!H2:H700;38;Blad5!E2:E3) not what you need?

[dogberry]

Hi

Sorry but we cant edit a picture if you would like to upload a sample worksheet Im sure someone will help you out.


Chris

[dilipandey]

Hi psykfall123,

Welcome to the forum.

In a first glimpse, I can see that the range in second formula is not correct.. try with Blad5!E2:E700. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

Hi psykfall123,

Welcome to the forum.

In a first glimpse, I can see that the range in second formula is not correct.. try with Blad5!E2:E700. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[psykfall123]

Accually i tried with E700 also. It doesnt work. I get some strange values all the time. But it works with the first code. I cant see any logic in my problem.

[Ron Coderre]

An oddity of the SUMIF function is that the 3rd argument (Blad5!E2:E3) automatically expands to reference a range of the same size as the 1st argument, without changing the display of that reference.
Consequently, even though you entered: =SUMMA.OM(Blad5!H2:H700;"=38";Blad5!E2:E3)
Excel intereprets it as: =SUMMA.OM(Blad5!H2:H700;"=38";Blad5!E2:E700)

Does that help?

[psykfall123]

I have changed the code now to =SUMMA.OM(Månad!H2:H700;519;Månad!E2:E700) and i have renamed Sheet5 to "Månad". But it still doesnt work. The number 38 and 519 are different membernumers and it gives strange numbers all the time.

[Ron Coderre]

I think you'll need to post some sample data that exhibits the problem.