Count If Formula with a Defined Range
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Count If Formula with a Defined Range

  1. #1
    Board Regular
    Join Date
    May 2007
    Posts
    571
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    May 2007
    Posts
    571
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,080
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default 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. #5
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count If Formula with a Defined Range

    Quote Originally Posted by Special-K99 View Post
    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. #6
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count If Formula with a Defined Range

    Quote Originally Posted by Special-K99 View Post
    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. #7
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular
    Join Date
    May 2007
    Posts
    571
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    Board Regular
    Join Date
    May 2007
    Posts
    571
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count If Formula with a Defined Range

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

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,934
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Count If Formula with a Defined Range

    Quote Originally Posted by squeakums View Post
    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
    In future please do not duplicate your questions in separate threads.
    Last edited by Fluff; Aug 22nd, 2019 at 02:13 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •