The formula does not work, when the data are at the ends of columns

[bines53]

Hello friends,

This function does not work,=SUM(INDEX(($A$1:$E$644=I1)*($B$1:$F$644=J1),0))
And this function, works =SUM(INDEX(($A$1:$E$644=I2)*($B$1:$F$644=J2),0))

Do not understand why, can anyone help?

Thank you !

 

[John()]

Both your formulas work .... i believe though you are not getting the results you expected though. and i cant explain why as its very complicated ... im sure some of the ninjas can but if you look at the spread sheet you can see it suming up the 5 and 17 that connect .... but your other formula has no connections so it gives a result of 0 which is why you think its not working
you should also post what you were expecting for the results and then you can be given a solution

if you replace E1 which has the number 18 with a 14 your formula that you think is not working gives a result of 1 and then also replace E4 with a 14 your formula then gives a 2 .... which to me shows its working

 

[bines53]

Hi John,

Apparently, I was not clear enough,The formula in cell M6, the answer should be 3.


Thank you !

 

[John()]

Here is one way of getting that result but its not the only way and im sure someone can give you a better way but while youre waiting on a another response it will work and keep you going

 

[NARAYANK991]

Hi David ,

The reason the formula does not work in one case while it works in the other is very straightforward ; the formula is being used without an understanding of how it works !

It all comes back to what I have been saying in the past ; use Excel to implement correct logic , which means first draft the logic in plain English , verify that it is complete and correct , and then see how this logic can be converted to Excel formulae.

What the formula is doing is creating 2 matrices ; each matrix is 644 rows by 5 columns ; after creating these matrices , it is doing an arithmetic multiplication , not a matrix multiplication , which means the first element of the first matrix is multiplied by the first element of the second matrix , for all the elements.

Thus a 1 will result if an element in row x and column y of the first matrix is equal to the first check value , say the value in I1 or I2 , and if the corresponding element ( row x and column y ) of the second matrix is equal to the second check value , which is the value in J1 or J2.

By coincidence , for the second formula you have chosen values which are consecutively arranged ! Thus , because the two check values occur consecutively , the positions are the same in both matrices , but this is pure coincidence. For the second formula , instead of 17 , choose 18 and see what you get. Thus , the formula will give an expected result only if the 2 check values occur in the same position in both matrices i.e. if the two check values are always next to each other in the same orientation , meaning the first check value should occur first , immediately followed by the second check value.

The problem is knowing what you want ; I never start with a formula and decipher why it works or does not work ; I always start with the logic of what is to be done , and see how a formula can be built using the basic building blocks , which will implement the logic.

Mastery of the basic building blocks , and their combinations is a must before one tries to develop complicated / complex formulae.

Narayan

 

[bines53]

Hi John,

Simpler way,=SUM(--(MMULT(--COUNTIF(I1:J1,$A$1:$F$11),{1;0;0;0;0;1})=2))

or,=SUM(--(MMULT(--COUNTIF(I1:J1,$A$1:$F$11),{1;1;1;1;1;1})=2))

Thank you !

 

[bines53]

Hi Narayan,

=SUM(INDEX(($A$1:$E$644=I1)*($B$1:$F$644=J1),0)) ,Is exactly to
=COUNTIFS($A$1:$E$11,I1,$B$1:$F$11,J1).

You know a way, how do I implement =max(COUNTIFS($A$1:A11,I2,$B$1:$F$11,J2)) ?


Thank you !

 

[John()]

Hi John,

Simpler way,=SUM(--(MMULT(--COUNTIF(I1:J1,$A$1:$F$11),{1;0;0;0;0;1})=2))

Thank you !

Nice havent used ...MMULT ... function yet ... havent even looked up to find out how it works ... have seen it in a formula but was a very complex formula so left it till im better at breaking down complex formulas.

this though was nice as it was easy to see what it was doing so i was able to understand what was going on.

couple of observations .... Persume it dose exactly what you wanted, so in that case your problem is solved
But if this is the case why the other part ... as it seemed from your initial post you wanted a single formula that could do both ... now you are back in the same situation with a formula that can only solve one of the problems eg this formula will only find the matches in either end columns.
it will not find the six matches for 5 and 17 ... even if you change the formula it will only ever give a total of 5 not 6 as your other one did. so you cant use this formula for that.

Now that i have seen MMULT in action i must go figure out where else i can make use of it.