Match, Index and CSV

[shapper]

Hello,

On the cell H3 I have teh following:
=IF(ISNA(INDEX(B$47:K$52;MATCH($E3;B$47:B$52;0);5)); 0; INDEX(B$47:K
$52;MATCH($E3;B$47:B$52;0);5))

Basically, it looks for a match of E3 value in range B$47:B$52 and
gets the value the 5th column.

At the moment a possible value of E3 is "COM".
And there is only one "COM" value in the range list.

What I would like is in E3 to be able to have "COM" or "COM,NET".

When there are two values in E3 I would get the SUM of the values of
the 5th column in the range that match the values COM and NET.

How can I change my code to do this?

Thanks,
Miguel

 

[Rick Rothstein]

First off, if I understand what you originally wanted correctly, this
formula would have done what your first formula did...

=SUMPRODUCT((B47:B52=E3)*F47:F52)

Note that if the value in E3 appeared in F47:F52 more than once, then all
values from Column F in the row where the matches took place would be
summed; however, you said there was only one occurrence, so this shouldn't
really matter to you (I just mentioned it for completeness sake). Now, for
your current question, I think this formula will do what you want...

=SUMPRODUCT(ISNUMBER(SEARCH(","&B47:B52&",";","&E3&","))*F47:F52)

Note that is has the same multiple cell functionality as the above formula
(but again, you said there would only be one occurrence of each key word in
F47:F52, so again, this shouldn't matter to you).

 

[Bernard Liengme]

If you are always add what is in column F
=SUMPRODUCT((B47:B52=LEFT(E3,3))+((B47:B52=RIGHT(E3,3))*LEN(E3)=7),F47:F52)

If you want to control the column F is column 6)
=SUMPRODUCT(((B47:B52=LEFT(E3,3))+((B47:B52=RIGHT(E3,3))*LEN(E3)=7))*(COLUMN(C47:K52)=6)*C47:K52)

best wishes

 

[shapper]

First off, if I understand what you originally wanted correctly, this
formula would have done what your first formula did...

=SUMPRODUCT((B47:B52=E3)*F47:F52)

Your suggestions are really good and I am updating my code. I have a
problem with this one:
=(B$41:B$44=D3)*E$41:E$44

Note: In my worksheet cells B41 to B44 are always merged with its
correspondent C cell.
So B41 is merged with C41, B42 is merged with C42, and so on.
Is this causing some problem in my formular?

Note that if the value in E3 appeared in F47:F52 more than once, then all
values from Column F in the row where the matches took place would be
summed; however, you said there was only one occurrence, so this shouldn't
really matter to you (I just mentioned it for completeness sake). Now, for
your current question, I think this formula will do what you want...

=SUMPRODUCT(ISNUMBER(SEARCH(","&B47:B52&",";","&E3&","))*F47:F52)

This worked fine. I just needed a second term:

=SUMPRODUCT(ISNUMBER(SEARCH(","&B$47:B$52&",";","&E3&","))*F$47:F$52)
+ SUMPRODUCT(ISNUMBER(SEARCH(","&B$47:B$52&",";","&E3&","))*G$47:G
$52/$I$26)

Basically I am also getting the values in G columns but these ones
must be divided by I26 and then I sum both terms.

Is there a way to simplify this code even more?

Thank You,
Miguel