sum column based on condition with pipe divide & highlight code missing

[nd4spd]

Hi, I am looking to find a solution below if someone could help please,

1. On the report sheet I wish to sum column from sheet data based on condition in column B on the report with condition divided by pipe | .
2. I also would like to highlight code missing in the report sheet on data tab highlighted in red.

I have attached an example

Thank you

[Glenn Kennedy]

No you haven't.... try again, after reading the yellow banner (top of page).

[nd4spd]

Thanks Glenn uploaded the file

[Glenn Kennedy]

Now you have!!

Part 1:

=SUMPRODUCT(SUMIF(Data!B:B,FILTERXML("<a><b>"&SUBSTITUTE(B3,"|","</b><b>")&"</b></a>","//b"),Data!C:C))

copied down.

[Glenn Kennedy]

In your real file, are there just 2 rows (B3 & B4) or many?

[nd4spd]

Thanks Glenn there are 2 more not relevant just descriptions for these codes.

Code, Name, $year, $month

[Glenn Kennedy]

I THINK you may have misunderstood my Q. You have 1 row:
10|123|4321

and another row:
11|12

are there any/many more. Of course, I may have misunderstood your answer!!

[nd4spd]

sorry yes there are many more codes but all unique and none repeated.

[Bo_Ry]

1. C3
=SUMPRODUCT(ISNUMBER(FIND("|"&Data!$B$4:$B$9&"|","|"&B3&"|"))*Data!$C$4:$C$9)

But Glenn's FILTERXML formula is faster.

2. Conditional formatting formula applies to B4:B9
=NOT(COUNT(FIND("|"&B4&"|","|"&report!$B$3:$B$5&"|")))

[nd4spd]

@ Bo_RY The conditional format seems to be working on some but not all even though I have tweaked it for the range "puzzled ?"

Please Login or Register  to view this content.

[JeteMc]

Assuming that the formatting still starting in cell B4 on the DATA sheet and that the codes on the REPORT sheet are found in cells B3:B900, try the following:

Please Login or Register  to view this content.

Let us know if you have any questions.