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

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History