Hey there, here is my problem: I've defined a name to represent a large number of rows and columns. I'm trying to make a formula that will count the number of instances of a given name in a particular column.

I could just specify the column in the reference but that would require a different name for each column and there are lots. Data is constantly being added so I wanted to be able to have a limited number of names that I could easily change and thus change every formula in one shot.

For example:

A X Y

B Y X

C Z ZI want to count the number of 'Y's in columns 2 and 3 using the name "Current" which represents the first two rows or A1:B2.

It seems like I've tried everything. Is this possible?

Thanks!

I'm a little confused as to how a range of A1:B2 can have a 3rd column. So, ignoring that, and assuming you really want to reference columns 2 and 3 of a named range, I think this is what you want:

=COUNTIF(INDEX(Current,,2):INDEX(Current,,3),"=Y")

Sorry for the confusion. I should have said A1:C2. In any case, the formula worked beautifully and gave me the hint I needed to properly research the topic instead of blindly searching through formulas.

Thanks!

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History