LOOKUP OR FIND WITH SUMUP COMBINED

Marilen.Navarro103391

Hello,

Hope you could help me.

On Sheet 1, "Future Column", I would like to find in Sheet 2 all similar names against sheet 1.

Ex. In Sheet 1 - BOFA, BOFACBRE, BOFAJLL is on Sheet 2 all in Different rows. I would like to combine and sum up as for Sheet 1 "Future" is equal to 9.

SHEET 1

SHEET 2

Paul Newcome

You are going to want to use something along the lines of...

=SUMIFS({Sheet 2 Future Column}, {Sheet 2 XREFCS Column}, CONTAINS(@cell, [XREF/XREFS]@row))

Paul Newcome

Make sure you are using the same delimiter between each entry and try removing the spaces. It is working on my sheets.

Paul Newcome

You are going to want to use something along the lines of...

=SUMIFS({Sheet 2 Future Column}, {Sheet 2 XREFCS Column}, CONTAINS(@cell, [XREF/XREFS]@row))

Marilen.Navarro103391

THANK YOU. YOU'RE THE BEST PAUL !!!

Marilen.Navarro103391

Another question.. I'm having different result when Sheet 2 have a missing XREFCS. I have to manually remove in Sheet one the XREF/XREFS.

Sheet 1 is constant, will not change.

Thanks Again.

Paul Newcome

I'm not sure I understand exactly what you mean... Can you provide a screenshot example?

Marilen.Navarro103391

Hi Paul,

In sheet 2, we have BOFA, BOFACBRE and BOFAJLL, the sum in Future in Sheet 2 for those I mentioned is 9. However in ......

Drawing 1 : SUM is 16

Drawing 2 SUM IN 9 - CORRECT ONE BELOW. I removed the other Names in XREF/XREFS

Paul Newcome

Is it possible that those two additional names in sheet one are in another cell on sheet two? When I did my testing, it worked fine for me.

Marilen.Navarro103391

I checked there's no additional names.

When I removed BOFALHC, BOFACBREASCO, BOFACSH, BOFAMILLER and leave BOFA, BOFACBRE and BOFAJLL. The sum will be 9 which is correct.

Do you think I have to write the names one by one to get exact sum?

Paul Newcome

Make sure you are using the same delimiter between each entry and try removing the spaces. It is working on my sheets.