SUMPRODUCT to compare ranges

[Phil Smith]

After much headbanging and lots of reworded web searches, I have come up with the following formula,

=IF(MATCH("End",A:A)=16,"COVER",IF(SUMPRODUCT(--(B13:B100=Stations))>0,"RUNNING","DEPOT"))

only for it to produce an #N/A error.

I got the SUMPRODUCT part of formula from [url]https://exceljet.net/formula/range-contains-one-of-many-values
[/URL]
The first part, IF(MATCH("End",A:A)=16 works absolutely fine.

The last part, IF(SUMPRODUCT(--(B13:B100=Stations))>0,"RUNNING","DEPOT")) , is where it throws up the #N/A error

I couldn't work out how to reference a dynamic range for column B using MATCH("End",A:A) to find the final row. Therefore I have used a static range of B13:B100 will capture more than enough data to make a comparison to my named range, "Stations".

If there is ANY match at all between the data in B13:B100 and the named range "Stations", then the cell should show "RUNNING", otherwise "DEPOT".

Can anyone enlighten me to what is causing the error and how to rectify it?

 

[XOR LX]

Re: Help requested with SUMPRODUCT to compare ranges

Hi,

I imagine the most likely cause is that you've set up Stations as a vertical range, as opposed to a horizontal one.

B13:B100 and Stations need to be orthogonal to each other; since the former is clearly a vertical range, the latter needs to be a horizontal range.

Regards

 

[Phil Smith]

Re: Help requested with SUMPRODUCT to compare ranges

Thank you XOR LX. I have now learnt two things.

One, that the two ranges being compared need to be orthogonal.
Two, as a result of your post, that orthogonal means at right angles!

I will make those changes tomorrow - at work on a late shift now - and I will let you know.

Thank you!

 

[XOR LX]

Re: Help requested with SUMPRODUCT to compare ranges

Two, as a result of your post, that orthogonal means at right angles!

I will make those changes tomorrow - at work on a late shift now - and I will let you know.

Ok, buon lavoro!

Cheers

 

[Phil Smith]

Re: Help requested with SUMPRODUCT to compare ranges

Hi XOR LX. It worked like a dream. Thank you so much!

 

[XOR LX]

Re: Help requested with SUMPRODUCT to compare ranges

Glad to hear it!

And cheers to you too!