# Using defined name as criteria in formula

1. ## Using defined name as criteria in formula

Hi,

I am using the following formula to look for grades that have been achieved across multiple sheets. ``Please Login or Register  to view this content.``
I have a list of grades that i have defined as the name GRDS.

This is the list of grades
1
2
3
4
5
6
7
8
9
A*
A
B
C
D
E
F
G
P
M
D
D*
L1
L2

But for some reason it only seems to return the number of values of 1's? is this due to the grades being a combination of numbers and letters?

Thanks

Jamidd  Register To Reply

2. ## Re: Using defined name as criteria in formula

Works OK in my test workbook -- with an important twist:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheets&"'!\$B\$12:\$B\$51"),A2,INDIRECT("'"&Sheets&"'!\$F\$12:\$F\$51"),SUBSTITUTE(GRDS,"*","~*")))

Also, there are two D's in your GRDS: after C and after M.  Register To Reply

3. ## Re: Using defined name as criteria in formula

Thank you for your response Root!

Unfortunately, i can only seem to get the formula to work with 1's not the other grade values. If i place any of the other grades the formula does not seem to count? but if the grade is a 1 the formula does count!

have you got an suggestions?  Register To Reply

4. ## Re: Using defined name as criteria in formula

Here is my test workbook -- try playing with it.  Register To Reply

5. ## Re: Using defined name as criteria in formula

If it's currently A2:A24, make that list go A2:X2 instead..

Edit, or the list of sheets as Root did.  Register To Reply