# Thread: Count If Formula with a Defined Range Thanks: 0 Likes:  1 Post #5330083 (1)

1. ## Count If Formula with a Defined Range

How would you write a countif formula using a defined name range?

My range: =AFME!\$G\$18:\$G\$19,AFME!\$G\$25,AFME!\$G\$43,AFME!\$G\$45,AFME!\$G\$46:\$G\$48,AFME!\$G\$50:\$G\$51,AFME!\$G\$54:\$G\$58,AFME!\$G\$60,AFME!\$G \$62:\$G\$64,AFME!\$G\$66,AFME!\$G\$68:\$G\$72,AFME!\$G\$76:\$G\$77,AFME!\$G\$75:\$G\$77,AFME!\$G\$78,AFME!\$G\$81:\$G\$84,AFME!\$G\$144:\$G\$146,A FME!\$G\$149,AFME!\$G\$149,AFME!\$G\$150,AFME!\$G\$183:\$G\$184,AFME!\$G\$186,AFME!\$G\$193:\$G\$194,AFME!\$G\$198,AFME!\$G\$200:\$G\$201,AFME !\$G\$255:\$G\$257,AFME!\$G\$269:\$G\$270,AFME!\$G\$272,AFME!\$G\$274,AFME!\$G\$276,AFME!\$G\$278,AFME!\$G\$280,AFME!\$G\$285,AFME!\$G\$298:\$G \$299,AFME!\$G\$301,AFME!\$G\$303,AFME!\$G\$305,AFME!\$G\$307,AFME!\$G\$310,AFME!\$G\$312,AFME!\$G\$318,AFME!\$G\$320,AFME!\$G\$322,AFME!\$G \$324,AFME!\$G\$326,AFME!\$G\$328,AFME!\$G\$331,AFME!\$G\$334,AFME!\$G\$335,AFME!\$G\$336,AFME!\$G\$340:\$G\$341,AFME!\$G\$351,AFME!\$G\$353: \$G\$359,AFME!\$G\$366:\$G\$368,AFME!\$G\$375:\$G\$382,AFME!\$G\$384:\$G\$387,AFME!\$G\$413:\$G\$414,AFME!\$G\$451,AFME!\$G\$453,AFME!\$G\$457,A FME!\$G\$458,AFME!\$G\$462,AFME!\$G\$464:\$G\$465,AFME!\$G\$468:\$G\$472,AFME!\$G\$475,AFME!\$G\$479,AFME!\$G\$511,AFME!\$G\$518,AFME!\$G\$526 ,AFME!\$G\$534:\$G\$535,AFME!\$G\$537,AFME!\$G\$555,AFME!\$G\$561,AFME!\$G\$565,AFME!\$G\$569,AFME!\$G\$572,AFME!\$G\$575:\$G\$581,AFME!\$G\$5 85,AFME!\$G\$589,AFME!\$G\$593,AFME!\$G\$596,AFME!\$G\$599:\$G\$606,AFME!\$G\$608:\$G\$610,AFME!\$G\$612:\$G\$617,AFME!\$G\$1118,AFME!\$G\$113 4,AFME!\$G\$1153,AFME!\$G\$1158,AFME!\$G\$1182:\$G\$1183,AFME!\$G\$1214,AFME!\$G\$1218,AFME!\$G\$1232:\$G\$1234,AFME!\$G\$1236:\$G\$1242,AFM E!\$G\$1247,AFME!\$G\$1251:\$G\$1255,AFME!\$G\$1257,AFME!\$G\$1260,AFME!\$G\$1262,AFME!\$G\$1266:\$G\$1269,AFME!\$G\$1271,AFME!\$G\$1273:\$G\$ 1275,AFME!\$G\$1278:\$G\$1281,AFME!\$G\$1283:\$G\$1285,AFME!\$G\$1296:\$G\$1297,AFME!\$G\$1306,AFME!\$G\$1315:\$G\$1316,AFME!\$G\$1322,AFME! \$G\$1334,AFME!\$G\$1336:\$G\$1337

That's why its defined to just = YG

I wrote this formula and it doesn't work:

=COUNTIF(YG,"Satisfactory")

Help? Thanks!

2. ## Re: Count If Formula with a Defined Range

I tested the same thing and it seems to work fine. I used a much smaller range though.

does the capitalization and punctuation match the cells?

Are you receiving an error?

3. ## Re: Count If Formula with a Defined Range

Okay, so for example it works fine if I use a small range of g2:g4 connected, its when I start using the defined range with g2:g4, g8, g9:g11.. it doesn't work. But, that's the reason I'm trying to use a defined range because I'm pulling for 100's of cells that aren't connected unfortunately.

4. ## Re: Count If Formula with a Defined Range

You need to define "it doesn't work" more specifically.

Is this the cause?
AFME!\$G\$18:\$G\$19,AFME!\$G\$25,AFME!\$G\$43,AFME!\$G\$45,AFME!\$G\$46:\$G\$48,AFME!\$G\$50:\$G\$51,AFME!\$G\$54:\$G\$58,AFME!\$G\$60,AFME!\$G \$62:\$G\$64,AFME!\$G\$66,AFME!\$G\$68:\$G\$72,AFME!\$G\$76:\$G\$77,AFME!\$G\$75:\$G\$77,AFME!\$G\$78,AFME!\$G\$81:\$G\$84,AFME!\$G\$144:\$G\$146,A FME!\$G\$149,AFME!\$G\$149,AFME!\$G\$150,AFME!\$G\$183:\$G\$184,AFME!\$G\$186,AFME!\$G\$193:\$G\$194,AFME!\$G\$198,AFME!\$G\$200:\$G\$201,AFME !\$G\$255:\$G\$257,AFME!\$G\$269:\$G\$270,AFME!\$G\$272,AFME!\$G\$274,AFME!\$G\$276,AFME!\$G\$278,AFME!\$G\$280,AFME!\$G\$285,AFME!\$G\$298:\$G \$299,AFME!\$G\$301,AFME!\$G\$303,AFME!\$G\$305,AFME!\$G\$307,AFME!\$G\$310,AFME!\$G\$312,AFME!\$G\$318,AFME!\$G\$320,AFME!\$G\$322,AFME!\$G \$324,AFME!\$G\$326,AFME!\$G\$328,AFME!\$G\$331,AFME!\$G\$334,AFME!\$G\$335,AFME!\$G\$336,AFME!\$G\$340:\$G\$341,AFME!\$G\$351,AFME!\$G\$353: \$G\$359,AFME!\$G\$366:\$G\$368,AFME!\$G\$375:\$G\$382,AFME!\$G\$384:\$G\$387,AFME!\$G\$413:\$G\$414,AFME!\$G\$451,AFME!\$G\$453,AFME!\$G\$457,A FME!\$G\$458,AFME!\$G\$462,AFME!\$G\$464:\$G\$465,AFME!\$G\$468:\$G\$472,AFME!\$G\$475,AFME!\$G\$479,AFME!\$G\$511,AFME!\$G\$518,AFME!\$G\$526 ,AFME!\$G\$534:\$G\$535,AFME!\$G\$537,AFME!\$G\$555,AFME!\$G\$561,AFME!\$G\$565,AFME!\$G\$569,AFME!\$G\$572,AFME!\$G\$575:\$G\$581,AFME!\$G\$5 85,AFME!\$G\$589,AFME!\$G\$593,AFME!\$G\$596,AFME!\$G\$599:\$G\$606,AFME!\$G\$608:\$G\$610,AFME!\$G\$612:\$G\$617,AFME!\$G\$1118,AFME!\$G\$113 4,AFME!\$G\$1153,AFME!\$G\$1158,AFME!\$G\$1182:\$G\$1183,AFME!\$G\$1214,AFME!\$G\$1218,AFME!\$G\$1232:\$G\$1234,AFME!\$G\$1236:\$G\$1242,AFM E!\$G\$1247,AFME!\$G\$1251:\$G\$1255,AFME!\$G\$1257,AFME!\$G\$1260,AFME!\$G\$1262,AFME!\$G\$1266:\$G\$1269,AFME!\$G\$1271,AFME!\$G\$1273:\$G\$ 1275,AFME!\$G\$1278:\$G\$1281,AFME!\$G\$1283:\$G\$1285,AFME!\$G\$1296:\$G\$1297,AFME!\$G\$1306,AFME!\$G\$1315:\$G\$1316,AFME!\$G\$1322,AFME! \$G\$1334,AFME!\$G\$1336:\$G\$1337

5. ## Re: Count If Formula with a Defined Range

Originally Posted by Special-K99
You need to define "it doesn't work" more specifically.

Is this the cause?
AFME!\$G\$18:\$G\$19,AFME!\$G\$25,AFME!\$G\$43,AFME!\$G\$45,AFME!\$G\$46:\$G\$48,AFME!\$G\$50:\$G\$51,AFME!\$G\$54:\$G\$58,AFME!\$G\$60,AFME!\$G \$62:\$G\$64,AFME!\$G\$66,AFME!\$G\$68:\$G\$72,AFME!\$G\$76:\$G\$77,AFME!\$G\$75:\$G\$77,AFME!\$G\$78,AFME!\$G\$81:\$G\$84,AFME!\$G\$144:\$G\$146,A FME!\$G\$149,AFME!\$G\$149,AFME!\$G\$150,AFME!\$G\$183:\$G\$184,AFME!\$G\$186,AFME!\$G\$193:\$G\$194,AFME!\$G\$198,AFME!\$G\$200:\$G\$201,AFME !\$G\$255:\$G\$257,AFME!\$G\$269:\$G\$270,AFME!\$G\$272,AFME!\$G\$274,AFME!\$G\$276,AFME!\$G\$278,AFME!\$G\$280,AFME!\$G\$285,AFME!\$G\$298:\$G \$299,AFME!\$G\$301,AFME!\$G\$303,AFME!\$G\$305,AFME!\$G\$307,AFME!\$G\$310,AFME!\$G\$312,AFME!\$G\$318,AFME!\$G\$320,AFME!\$G\$322,AFME!\$G \$324,AFME!\$G\$326,AFME!\$G\$328,AFME!\$G\$331,AFME!\$G\$334,AFME!\$G\$335,AFME!\$G\$336,AFME!\$G\$340:\$G\$341,AFME!\$G\$351,AFME!\$G\$353: \$G\$359,AFME!\$G\$366:\$G\$368,AFME!\$G\$375:\$G\$382,AFME!\$G\$384:\$G\$387,AFME!\$G\$413:\$G\$414,AFME!\$G\$451,AFME!\$G\$453,AFME!\$G\$457,A FME!\$G\$458,AFME!\$G\$462,AFME!\$G\$464:\$G\$465,AFME!\$G\$468:\$G\$472,AFME!\$G\$475,AFME!\$G\$479,AFME!\$G\$511,AFME!\$G\$518,AFME!\$G\$526 ,AFME!\$G\$534:\$G\$535,AFME!\$G\$537,AFME!\$G\$555,AFME!\$G\$561,AFME!\$G\$565,AFME!\$G\$569,AFME!\$G\$572,AFME!\$G\$575:\$G\$581,AFME!\$G\$5 85,AFME!\$G\$589,AFME!\$G\$593,AFME!\$G\$596,AFME!\$G\$599:\$G\$606,AFME!\$G\$608:\$G\$610,AFME!\$G\$612:\$G\$617,AFME!\$G\$1118,AFME!\$G\$113 4,AFME!\$G\$1153,AFME!\$G\$1158,AFME!\$G\$1182:\$G\$1183,AFME!\$G\$1214,AFME!\$G\$1218,AFME!\$G\$1232:\$G\$1234,AFME!\$G\$1236:\$G\$1242,AFM E!\$G\$1247,AFME!\$G\$1251:\$G\$1255,AFME!\$G\$1257,AFME!\$G\$1260,AFME!\$G\$1262,AFME!\$G\$1266:\$G\$1269,AFME!\$G\$1271,AFME!\$G\$1273:\$G\$ 1275,AFME!\$G\$1278:\$G\$1281,AFME!\$G\$1283:\$G\$1285,AFME!\$G\$1296:\$G\$1297,AFME!\$G\$1306,AFME!\$G\$1315:\$G\$1316,AFME!\$G\$1322,AFME! \$G\$1334,AFME!\$G\$1336:\$G\$1337
G149 is also listed twice. Double check there's no over laps

6. ## Re: Count If Formula with a Defined Range

Originally Posted by Special-K99
You need to define "it doesn't work" more specifically.

Is this the cause?
AFME!\$G\$18:\$G\$19,AFME!\$G\$25,AFME!\$G\$43,AFME!\$G\$45,AFME!\$G\$46:\$G\$48,AFME!\$G\$50:\$G\$51,AFME!\$G\$54:\$G\$58,AFME!\$G\$60,AFME!\$G \$62:\$G\$64,AFME!\$G\$66,AFME!\$G\$68:\$G\$72,AFME!\$G\$76:\$G\$77,AFME!\$G\$75:\$G\$77,AFME!\$G\$78,AFME!\$G\$81:\$G\$84,AFME!\$G\$144:\$G\$146,A FME!\$G\$149,AFME!\$G\$149,AFME!\$G\$150,AFME!\$G\$183:\$G\$184,AFME!\$G\$186,AFME!\$G\$193:\$G\$194,AFME!\$G\$198,AFME!\$G\$200:\$G\$201,AFME !\$G\$255:\$G\$257,AFME!\$G\$269:\$G\$270,AFME!\$G\$272,AFME!\$G\$274,AFME!\$G\$276,AFME!\$G\$278,AFME!\$G\$280,AFME!\$G\$285,AFME!\$G\$298:\$G \$299,AFME!\$G\$301,AFME!\$G\$303,AFME!\$G\$305,AFME!\$G\$307,AFME!\$G\$310,AFME!\$G\$312,AFME!\$G\$318,AFME!\$G\$320,AFME!\$G\$322,AFME!\$G \$324,AFME!\$G\$326,AFME!\$G\$328,AFME!\$G\$331,AFME!\$G\$334,AFME!\$G\$335,AFME!\$G\$336,AFME!\$G\$340:\$G\$341,AFME!\$G\$351,AFME!\$G\$353: \$G\$359,AFME!\$G\$366:\$G\$368,AFME!\$G\$375:\$G\$382,AFME!\$G\$384:\$G\$387,AFME!\$G\$413:\$G\$414,AFME!\$G\$451,AFME!\$G\$453,AFME!\$G\$457,A FME!\$G\$458,AFME!\$G\$462,AFME!\$G\$464:\$G\$465,AFME!\$G\$468:\$G\$472,AFME!\$G\$475,AFME!\$G\$479,AFME!\$G\$511,AFME!\$G\$518,AFME!\$G\$526 ,AFME!\$G\$534:\$G\$535,AFME!\$G\$537,AFME!\$G\$555,AFME!\$G\$561,AFME!\$G\$565,AFME!\$G\$569,AFME!\$G\$572,AFME!\$G\$575:\$G\$581,AFME!\$G\$5 85,AFME!\$G\$589,AFME!\$G\$593,AFME!\$G\$596,AFME!\$G\$599:\$G\$606,AFME!\$G\$608:\$G\$610,AFME!\$G\$612:\$G\$617,AFME!\$G\$1118,AFME!\$G\$113 4,AFME!\$G\$1153,AFME!\$G\$1158,AFME!\$G\$1182:\$G\$1183,AFME!\$G\$1214,AFME!\$G\$1218,AFME!\$G\$1232:\$G\$1234,AFME!\$G\$1236:\$G\$1242,AFM E!\$G\$1247,AFME!\$G\$1251:\$G\$1255,AFME!\$G\$1257,AFME!\$G\$1260,AFME!\$G\$1262,AFME!\$G\$1266:\$G\$1269,AFME!\$G\$1271,AFME!\$G\$1273:\$G\$ 1275,AFME!\$G\$1278:\$G\$1281,AFME!\$G\$1283:\$G\$1285,AFME!\$G\$1296:\$G\$1297,AFME!\$G\$1306,AFME!\$G\$1315:\$G\$1316,AFME!\$G\$1322,AFME! \$G\$1334,AFME!\$G\$1336:\$G\$1337
Also notice a space in 585, 1134,1275, ad 1334

7. ## Re: Count If Formula with a Defined Range

I just tested using ranges split up. I got a #Value error. I looked up similar situations and CountIF is not allowed across split ranges. Can you do just all of columnG?

8. ## Re: Count If Formula with a Defined Range

I believe that's the case and was afraid of that with the split ranges. I will do those few edits where no dups or spaces to see if it fixes, but all in all I think its because of that darn rule with countif's. I originally started with column G counts until they told me they only want to count rows that are green or yellow highlighted with the satisfactory, needs improvement, etc. Do you know of a work around that I can use a count if formula with colors and "satisfactory"?

9. ## Re: Count If Formula with a Defined Range

Yup, still a #value error... does anyone have a work around?

10. ## Re: Count If Formula with a Defined Range

Originally Posted by squeakums
I originally started with column G counts until they told me they only want to count rows that are green or yellow highlighted with the satisfactory, needs improvement, etc. Do you know of a work around that I can use a count if formula with colors and "satisfactory"?
This question has now been asked here https://www.mrexcel.com/forum/excel-...isfactory.html
All responses to the new question should be posted to the other thread.

@squeakums