Return Value if Equal to a List

[kumara_faith]

Hi,

I have the following table:

Book1
	C	D	E	F	G
2	Leave Type	Leave	Task 1	Task 2	Check
3	AL	AL	10	10	0
4	EL		5		20
5	MC			6	20
Sheet1

Cell Formulas
Range	Formula
G3:G5	G3	=IF(D3=C3:C5,0,SUM(E3:F3))
Dynamic array formulas.

In column G, I am trying to return as 0 if any of the value in column D equals to any of the value in column C. The first cell G3 seems to return the right result but cell F4 and F5 seems to return the incorrect value.

The correct results are as follows:

Book1
	C	D	E	F	G
8	Leave Type	Leave	Task 1	Task 2	Check
9	AL	AL	10	10	0
10	EL		5		5
11	MC			6	6
Sheet1

Appreciate any help to rectify the formula.

 

[Fluff]

How about

+Fluff 1.xlsm
	C	D	E	F	G
1
2	Leave Type	Leave	Task 1	Task 2	Check
3	AL	AL	10	10	0
4	EL		5		5
5	MC			6	6
Primary

Cell Formulas
Range	Formula
G3:G5	G3	=MMULT((D3:D5<>C3:C5)*E3:F5,{1;1})
Dynamic array formulas.

 

[jtakw]

Hi,

Not sure, would this also work:

Book3.xlsx
	C	D	E	F	G
1
2	Leave Type	Leave	Task 1	Task 2	Check
3	AL	AL	10	10	0
4	EL	MC	5		0
5	MC			6	6
Sheet1008

Cell Formulas
Range	Formula
G3:G5	G3	=IF(COUNTIF(C$3:C$5,D3),0,SUM(D3:F3))

 

[jtakw]

Too late to Edit, typo in my post above, change to this:

=IF(COUNTIF(C$3:C$5,D3),0,SUM(E3:F3))

 

[kumara_faith]

Hi Fluff and jtakw,

Thank you for your solution. Appreciate your patience and valuable time. Both worked. Have a great day ahead..?

 

[Fluff]

Glad to help & thanks for the feedback.

 

[jtakw]

You're welcome.