How to use Vlookup and HLookUP to set a range for CountIF

GABOCSA Source

I searched through the internet and StackOverflow but I did not find the exact relevant issue being resolved.

So I am trying to resolve the following issue.

I have a table with regions and some data in it.

I wanted to count how many Yes and No I have in the table for each region.

Example Table
enter image description here

My Formula looks like this so far but does not work;

=COUNTIF(VLOOKUP("UK",A2:G9,3,TRUE,C2:G9),"Y")

So, I should receive 1 as an answer because there is only one "Y" for UK in DATA 1 column.

excelexcel-formulavlookup

Answers

answered 2 years ago Jeeped #1

Use the INDEX function across all columns and determine the column for Data 1 with the MATCH function.

=COUNTIF(A2:A9, "UK", INDEX(A2:G9, 0, MATCH("data 1", 1:1, 0)), "Y")

Alternately, use the following and fill down.

=COUNTIF(A$2:A$9, "UK", INDEX(A$2:G$9, 0, MATCH("data " & ROW(1:1), $1:$1, 0)), "Y")

comments powered by Disqus