How to search for one value in a column against all values in multiple columns in Excel-CodePudding

I tried a lot to find a solution for the below issue, lets say I have column A,C,D ; I want to take the fist value in column A and check if it exist in C or D entire columns; if exist then I will return A, then take the second value in A and search in C & D columns and so on.

The bottom line, is that I want to find the count of each value in column A that existed in C or D

Thanks

CodePudding user response：

=COUNTIF(C:C,A1:A4) COUNTIF(D:D,A1:A4)

CodePudding user response：

Using SUMPRODUCT Function

Formula used in cell B2

=SUMPRODUCT((A2=$C$2:$C$7) (A2=$D$2:$D$7))

And Fill Down!