SUMPRODUCT formula not working when making small RIGHT/LEFT change

[Rusty315]

Hi

I'm using the following SUMPRODUCT formula (array formula) and it works fine:

{=SUMPRODUCT(--(RIGHT(Journey!$F$4:$F$1067,3)=F4),Journey!$H$4:$H$1067,
SUMIF(OFFSET(SOLD!$J$1,MATCH(LEFT(Journey!$F$4:$F$1067,3),SOLD!$I$2:$I$69,0),),">"&0))}

I need to use in in another part of my workbook and it needs to be in reverse and refers to a couple of different arrays that are the same size but for some reason, it doesn't work:

{=SUMPRODUCT(--(LEFT(Journey!$F$4:$F$1067,3)=F4),Journey!$I$4:$I$1067,
SUMIF(OFFSET(SOLD!$L$1,MATCH(RIGHT(Journey!$F$4:$F$1067,3),SOLD!$K$2:$K$69,0),),">"&0))}

The things I've changed are:
- the RIGHT and LEFT functions which I've switched around
- referring to column I instead of H on the journey sheet in the second formula
- referring to values in column K instead of I in the first sheet and made the appropriate change to the reference in the OFFSET function (changed from column J to L)

Contained in the cells/arrays in the formulas:

- Examples of values in the array Journey!$F$4:$F$1067 are MELSYD, BNEADL, PERDRW, etc. which are a combination of three-letter codes like SYD, PER, MEL, ETC.
- Value in F4 will always be one of three-letter codes like SYD, PER, MEL, etc.
- Columns H and I on the Journey sheet are all percentages.
- Values in SOLD!$K$2:$K$69 and SOLD!$I$2:$I$69 are three letter codes like SYD, PER, MEL, etc and there are numerical values next to each of those values (columns J and L).

For some reason, the changes aren't working. The values in all the arrays noted in the second formula are all of the same type.

I have no idea why it's not working so if anyone could help me out before I start growing my hair back just so I can pull it out again, I'd be very appreciative!

Cheers

 

[marious]

Assuming that your formula is correct sometimes what you see is not what it should be, particularly when you extract numbers from strings. Validate the data in for the new cells using for example LEN() and TYPE(), you might see 1 but n reality I "1 ", and the array will see txt rather than numbers. 2) You can try to copy and paste as values and then use text to columns "don't format with right click, sometimes it doesn't work" and format into the expected format "Text to columns will force the formatting" 3) If you see that data move to left side or to the right of the cell means different format.
I hope it helps
Marious

 

[barry houdini]

I can't see any obvious reason why that change wouldn't work but I think you could simplify your formula - does this do what you want?

=SUMPRODUCT(--(LEFT(Journey!$F$4:$F$1067,3)=F4),Journey!$I$4:$I$1067,
--(SUMIF(SOLD!$K$2:$K$69,RIGHT(Journey!$F$4:$F$1067,3),SOLD!$L$2:$L$69)>0))

 

[Rusty315]

Hi Barry, thanks for that suggestion. It doesn't quite do what I need it to but it may be because there's a problem with the arrays I'm referencing. I looked at the nested formulas within the SUMPRODUCT and checked their results in the formula bar with F9 and found that the MATCH section returned several N/A errors. I'm not sure why this is. I'm kinda new to SUMPRODUCT formulas so if you could shed some light on this for me, that'd be fantastic.

Thanks mate!

 

[Rusty315]

Hi again, I was checking some other posts on MATCH functions returning #N/A errors and fixed it by removing the 0 (exact match) from the end of the match formula. Seems to be working fine now.

Thanks for your response, Barry. It's much appreciated!

 

[barry houdini]

That's one adavantage of SUMIF in my suggestion over MATCH in yours, SUMIF will return zero rather than #N/A if there is no match. Did you expect some values to not match?

I don't think it's a good idea to remove 0 from the end. In the cases where you previously had #N/A those will now match, possibly with the wrong values, so you may get a result.....but perhaps not the correct one.

Can you explain what didn't work with my suggestion? Does SOLD!$K$2:$K$69 contain duplicate values?

 

[Rusty315]

I'm not sure why it didn't work. All the amounts listed were zeros. SOLD!$K$2:$K$69 doesn't contain duplicate values but I did tinker with it to make sure all possible three letter codes were listed in all arrays so that they did match and - hey presto - no more N/A# errors! The original formula is working fine now with the zero for exact matches included.

I do understand the advantage with the SUMIF formula. One #N/A error and it throws the whole thing out. I genuinely appreciate your help, Barry. I was pulling my hair out over this and you definitely helped shed some light on this for me. Thanks a bunch!