SUMPRODUCT + MID multiple sheets - not working?

[Camel123]

Hi,

Trying to sum multiple columns from another sheet based on one criteria, which is determined by the "MID" function. What is wrong with the formula?

1) B2 (formatted as text)=9132456, 6753456

2) 6753456 appears in D:D (formatted as text as well). It has 100$ in col. P and -10$ in Q. Should return 90$ then but I get #value !


{=SUMPRODUCT(--('export'!D:D=MID('upload'!B2;10;7)*('export'!P:Q)))}

Tried without {} and -- but makes no difference.

 

[tyija1995]

Hey, try this formula:

=SUMPRODUCT((export!D:D=VALUE(MID(upload!B2,10,7)))*(export!P:Q))

 

[Camel123]

Hey, try this formula:

=SUMPRODUCT((export!D:D=VALUE(MID(upload!B2,10,7)))*(export!P:Q))

Not working :/ tried without VALUE as well but not working.

 

[tyija1995]

What error are you getting? I noticed you used semi colons instead of commas to separate parameters so perhaps try that?

 

[steve the fish]

If you want to do an addition, eg:

=SUMPRODUCT(--(export!D1:D100=MID(upload!B2,10,7)),export!P1:P100+export!Q1:Q100)

 

[Camel123]

I know it has to do with the version that I am using is swedish and it seem to be different to others. ; is the same as your ,

 

[Camel123]

If you want to do an addition, eg:

=SUMPRODUCT(--(export!D1:D100=MID(upload!B2,10,7)),export!P1:P100+export!Q1:Q100)

Not working,

So, I replaced 9132456, 6753456 with letters "abcdefg, hijklmn" on all places and all of a sudden it worked, even though 9132456, 6753456 are formatted as text.

 

[tyija1995]

What happens if you nest steves MID formula in a VALUE?

=SUMPRODUCT(--(export!D1:D100=VALUE(MID(upload!B2;10;7))),export!P1:P100+export!Q1:Q100)

 

[Camel123]

What happens if you nest steves MID formula in a VALUE?

=SUMPRODUCT(--(export!D1:D100=VALUE(MID(upload!B2;10;7))),export!P1:P100+export!Q1:Q100)

Still getting #value ! error :/

 

[tyija1995]

Odd... I've tried both of the formulas posted by myself and Steve and they both work for me...

Is cell B2 on the upload sheet definitely populated? I'm just scratching my head as to why you're getting a #value ! error