Count If Formula with a Defined Range

squeakums

Well-known Member
Joined
May 15, 2007
Messages
677
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!
 

Some videos you may like

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
Joined
Aug 15, 2019
Messages
34
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
Joined
May 15, 2007
Messages
677
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
Joined
Nov 7, 2006
Messages
8,342
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
Joined
Aug 15, 2019
Messages
34
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
Joined
Aug 15, 2019
Messages
34
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
Joined
Aug 15, 2019
Messages
34
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
Joined
May 15, 2007
Messages
677
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"?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,499
Office Version
365
Platform
Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top