You only need as many COUNTIF and MATCH formulas as there are unique
entries in each column. So if you had "AA", "BB", "CC", "DD", you only
need four sets of formulas.

If you don't want to see ANYTHING if a particular value is not found,
then you need VBA. That's the only way to do it without having a cell
occupied by a formula.

--JP


On Oct 20, 9:59*am, Niju David
wrote:
Dear JP,
I dont want to put the list before hand because of 2 reasons;
1-I have got almost 1000 to 2000 of them
2-If there are no counts for a particular value then it will show zero or
blank, which i do no want.

Kindly help



"JP" wrote:
Why not put the criteria into a cell, and reference it from the
formula?

For example in C1, put "AA" and then reference it from the COUNTIF
cell.

=COUNTIF(OFFSET(Sheet1!A1,B1-1,0,1,6),C1)

Then you could just create a list of unique search items in column C
(C1: "AA", C2: "BB", etc) and fill down the COUNTIF formula to get
your counts.

--JP