# Count If Formula with a Defined Range

#### squeakums

##### Well-known Member
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!

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### SupremeDr

##### New Member
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

##### Well-known Member
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

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### New Member
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

##### Well-known Member
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

##### Well-known Member
Yup, still a #value error... does anyone have a work around?

#### Fluff

##### MrExcel MVP, Moderator
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

Last edited:

1,102,097
Messages
5,484,652
Members
407,459
Latest member
DICKSON KIMEMIA

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...