Matching values in four ranges

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
576
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Mackers

Well-known Member
Joined
Nov 4, 2013
Messages
536
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

Well-known Member
Joined
May 18, 2009
Messages
576
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 4, 2013
Messages
536
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
 
Last edited:

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
576
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 4, 2013
Messages
536
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

Well-known Member
Joined
May 18, 2009
Messages
576
Office Version
  1. 365
Platform
  1. Windows
Very helpful! Once again, many thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,691
Messages
5,597,562
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top