CODEWITHSUNDEEP
excel
premiumcopypaper
premiumcopypaper

Reputation: 195

Check entry against several columns

I've got a list of countries in Column A; I want to structure a formula in Column B, that will check if that country appears in lists that are unrelated to one another in Columns, C, D, E, and F. So if Argentina appears in Column C, D, E, and F, then the output in B1 would be TRUE.

I've tried: COUNTIFS(C2:C42, "Argentina", D2:D42, "Argentina", E2:E49, "Argentina", F2:F36, "Argentina") However I get a #VALUE error.

enter image description here

Upvotes: 2

Views: 37

Answers (2)

Vityata
Vityata

Reputation: 43593

In general, COUNTIFS() assumes that the values are on the same row. Thus, you should use it only in this scenaro.

Your formula is quite ok and should be working, but you should make sure that the ranges are with the same length. Currently the last 2 ranges finish on row 36 and 49 and the first two on row 42, thus you get the Excel error.

Here is how to do it:

=COUNTIFS(C2:C42,"Argentina", D2:D42,"Argentina",E2:E42,"Argentina", F2:F42, "Argentina")

Or in general, refer to the whole columns like this:

=COUNTIFS(C:C,"Argentina", D:D,"Argentina",E:E,"Argentina", F:F, "Argentina")

If you want a boolean value as an answer, you can simply write:

=IF(COUNTIFS(YOUR-FORMULA-HERE),TRUE,FALSE)

Upvotes: 1

pnuts
pnuts

Reputation: 59485

Assuming Argentina is in A1, please try in B1:

=AND(COUNTIF(C:C,A1)>0,COUNTIF(D:D,A1)>0,COUNTIF(E:E,A1)>0,COUNTIF(F:F,A1)>0)

Upvotes: 1

Related Questions