Count If Formula with a Defined Range

[squeakums]

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,AFME!$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,AFME!$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$585,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$1134,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,AFME!$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!

 

[SupremeDr]

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?

 

[squeakums]

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.

 

[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

 

[SupremeDr]

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

 

[SupremeDr]

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

 

[SupremeDr]

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?

 

[squeakums]

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"?

 

[squeakums]

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

 

[Fluff]

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...-formula-if-cell-yellow-has-satisfactory.html
All responses to the new question should be posted to the other thread.


@squeakums
In future please do not duplicate your questions in separate threads.