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.

 

[steve the fish]

If you are getting a value error I'd suggest either there is a value error in the data or there is text within the part you want to add.

 

[steve the fish]

Test the 100$ to check if its a number. Say its in P1 use:

=ISNUMBER(P1)

 

[Camel123]

Let me re-do everything and get back to you guys, if I don't manage to solve it I will post some screenshots on the evaluation.

 

[Camel123]

I evaluated the formula. The issue is caused by the sum range containing cells in text, such as "grand total" etc. Any workaround on this?

 

[tyija1995]

Try SUMIF instead:

=SUMIF(export!D:D;MID(upload!B2;10;7);export!P:P)+SUMIF(export!D:D;MID(upload!B2;10;7);export!Q:Q)

EDIT: Changed list separator to a ; from , (to fit your settings)

 

[Camel123]

It works, BUT since some values in column B only contain one number with 7 digits and not two, the MID returns BLANK and sums all values for all BLANKS in the criteria range, which should be ignored. :D

 

[tyija1995]

Are you dragging this formula down the column then? As I assumed it would only look at B2 specifically for middle characters

 

[Camel123]

I am dragging it down. I took the "MID" value *1 and it solved the issue, ignores the blanks now and returns correct values. Though I am surprised because using *1 should make the criteria a number, and the criteria range is numbers stored as text, but it still works so...

 

[tyija1995]

Excellent! Glad its sorted now then, got so used to using SUMPRODUCT now that I completely overlooked SUMIF until the last hour... Madness!

 

[Camel123]

Excellent! Glad its sorted now then, got so used to using SUMPRODUCT now that I completely overlooked SUMIF until the last hour... Madness!

Appreciate all efforts ? thank u all