Count number of rows where two columns values are matching and third column is null.

[deekhari]

Hi All,

I have a very strange condition where I have to get my answer in a single cell.
Note: I do not want to populate any randome cell and refer that in my cell function.

See the below table:

COLA COLB COLC COLD
123 123 Y
223 223 Y
223 223 Y
123 10-12-2010 123 Y
333 333 N

I need to COUNT number of columns where below condition met:
1) COLA=COLC
2) COLB is NULL
3) COLC having values in list (123,223)
4) COLD = "Y"

I have been using two function, but all four conditions are not met in both:

=SUMPRODUCT(--(COLC2:COLC6=COLA2:COLA6),--(COLD2:COLD6="Y"),--(COLB2:COL6<>"*"))

here I am not able to incoorporate condition 3)

=SUM(COUNTIFS(COLB2:COLB6,"=",COLD2:COLD6,"Y",COLC2:COLC6,{123;223}))
here I am not able to incorporate condition 1)

Please help me with this problem.

Thanks and Regards,
Dee

 

[baitmaster]

=SUMPRODUCT(--(C2:C6=A2:A6),--(D2:D6="Y"),--(B2:B6=""),COUNTIF(myList,C2:C6))

note that I've changed the column B condition, as your requirement states it must be NULL and I wasn't getting that using <>"*"

you'll need a named range called myList, containing your values 123,223 etc