Matching values in four ranges

[ChristineJ]

I'd like help with a formula that would return "Correct" if true and "" (blank) if false. All of the cells in the ranges have numerical entries or are blank.


IF the values in BN846:BN865 equal those in the corresponding row in CW846:CW865
and the values in BP846:BP865 equal those in the corresponding row in CY846:CY865
and the values in BR846:BR865 equal those in the corresponding row in DA846:DA865
and the values in BY846:BY865 equal those in the corresponding row in DC846:DC865


BUT it should only check for equality in those rows in which the value in column DF in the same row is >440 and <461


If the value in a row in column DF is <441 or >460, it does not matter if the values above in the same row match or not.

 

[Mackers]

Hi

I think this is what you're after:

=SUMPRODUCT((BN846:BN865=CW846:CW865)*(BP846:BP865=CY846:CY865)*(BR846:BR865=DA846:DA865)*(BY846:BY865=DC846:DC865)*(DF846:DF865<461)*(DF846:DF865>440))

Hope that helps

Mackers

 

[ChristineJ]

I just tried it. There is an exact match for all of the rows in all four column pairs (for example, BN846:BN865 equals all values in the corresponding rows in CW846:CW865, etc.) and all of the values in DF in those same rows are 450. Your formula returns a value of 2. I'm not familiar with SUMPRODUCT - how should I interpret the 2?

Thanks so much for your reply!

 

[Mackers]

Hi

Essentially what the formula is doing is going row by row, seeing if BN equals CW, BP equals CY, BR equals DA, BY equals DC and DF is larger than 440 and less than 461. If this is all true, it returns a 1. If not, it returns a 0. A value of 2 means that there are exactly 2 rows in your range that meet all the criteria you specified.

If you want to go row by row and see if BN equals CW, BP equals CY etc, you can just use this much simpler formula in whatever cell you like in row 846 and then drag it down:

=IF(AND(BN846=CW846, BP846=CY846, BR846=DA846, BY846=DC846, DF846<461, DF846>440), "Correct", "")

-------

For more detail regarding SUMPRODUCT(), which is a slightly more advanced function, looking at the first part of the formula:

=SUMPRODUCT((BN846:BN865=CW846:CW865)*(BP846:BP865=CY846:CY865)....

The first, blue formula will return an array of TRUE and FALSE depending on whether the BN and CW on the same row are equal. The green does the same for BP and CY. So we get this (I have made up some results below):

=SUMPRODUCT(({TRUE, TRUE, TRUE, FALSE, TRUE, FALSE, FALSE, ... })*({FALSE, FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, ...})....

The multiplication turns TRUE and FALSE into 1 and 0, as follows:

=SUMPRODUCT(({1, 1, 1, 0, 1, 0, 0, ...})*({0, 0, 1, 1, 0, 1, 1, ...})....

It then multiplies the first value against the first value, the second against the second, the third against the third etc.:

=SUMPRODUCT({1*0, 0*1, 1*1, 0*1, 1*0, 0*1, 0*1, ...}...
=SUMPRODUCT({0, 0, 1, 0, 0, 0, 0, ...)

It then adds all these numbers together to get, in this case, 1. This is because the third row was the only one where BN equalled CW and BP equalled CY.

Hope that helps

Mackers

 

[ChristineJ]

Extremely helpful explanation! I completely understand now - did not know about the conversion of True and False to 1 and 0. It all makes sense now, and I can see another use for this function. Hate to admit it, but the reason I was only getting a 2 is that the last column reference I gave you as BY should have been BT - my error. It works wonderfully now, and I learned a lot. Appreciate your time in giving me such a thorough response.

 

[Mackers]

No problem, glad to help. Some additional fluff:

When Excel expects a Boolean input (TRUE/FALSE) it will interpret 1 as TRUE and 0 as FALSE, so if you say =IF(A2, "Pass", "Fail") and A2 contains a 1 it will return "Pass".

Regarding the multiplication changing the TRUE/FALSE to 1/0, you will also see similar things to turn numbers-formatted-as-text to numbers, for example the MID(), LEFT() and RIGHT() functions always return text even if they are cutting out numbers, so:

=LEFT("123", 1) will return "1", which is not the same as 1, and you can't match from text to numbers using MATCH() or VLOOKUP() etc. As such, people often do one of the following to turn the "1" into a 1:

=0+LEFT("123", 1)
=--LEFT("123", 1)
=VALUE(LEFT("123",1))

These three will return a number rather than text.

Hope that helps

Mackers

 

[ChristineJ]

Very helpful! Once again, many thanks.