+ Reply to Thread
Results 1 to 6 of 6

Trying to tolve my problem with COUNTIFS or other formula

  1. #1
    Forum Contributor
    Join Date
    02-20-2022
    Location
    Indonesia
    MS-Off Ver
    Excel 2016
    Posts
    103

    Trying to tolve my problem with COUNTIFS or other formula

    NO Cage's ABW KET RANGE ABW COUNT
    1 NA02 491,04 SPLIT 100-200 TOTAL CAGE (SPLIT COUNT ONE)
    2 NA05 710,99 SPLIT 200-300 TOTAL CAGE (SPLIT COUNT ONE)
    3 NA07 172,95 300-400 TOTAL CAGE (SPLIT COUNT ONE)
    4 NA08 266,66 400-500 TOTAL CAGE (SPLIT COUNT ONE)
    5 NA09 529,19 SPLIT 500-600 TOTAL CAGE (SPLIT COUNT ONE)
    6 NA12 463,06 SPLIT etc
    7 NB01 778,13 SPLIT
    8 NB02 927,84
    9 NB06 781,99 SPLIT
    10 NB07 721,78 SPLIT
    11 NB09 42,00
    12 NB10 935,68 SPLIT
    13 NB12 498,63
    14 NC01 121,93
    15 NC03 41,38
    16 NC05 779,26 SPLIT
    17 NC06 41,38
    18 NC07 41,58
    19 NC11 537,20 SPLIT
    20 ND01 571,45 SPLIT

    Hi Guys, Need your help to make formula to count total Cages by ABW RANGE, but if there is SPLIT in KET Colums, will add +1 additional cages.
    Thanks in advance guys, your help means alot

    Tanpa judul.png
    Last edited by Sultan1994; 02-20-2022 at 10:57 PM.

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,860

    Re: Trying to tolve my problem with COUNTIFS or other formula

    It's always best to attach a sample file and not a picture (please see the yellow banner at the top of the page). Also, the example file should include a mock-up of your expected answers. I'm not sure I understand what you're asking - Are you wanting to count the number of Cage's where the ABW value is within a certain range? And add 1 for each that has "SPLIT" in the Keterangan column?

    In general, maybe something like:

    =Countifs(C2:C22,">="&Lower Range,C2:C22,"<="&Upper Range) + Countifs(C2:C22,">="&Lower Range,C2:C22,"<="&Upper Range,D2:D22="SPLIT")

  3. #3
    Forum Contributor
    Join Date
    02-20-2022
    Location
    Indonesia
    MS-Off Ver
    Excel 2016
    Posts
    103

    Re: Trying to tolve my problem with COUNTIFS or other formula

    YESS, but i want to range it between 100-200, 200-300, and next. but if there is split in range 100-200, 200-300 etc count as 1 additional cage. hope you understand
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,860

    Re: Trying to tolve my problem with COUNTIFS or other formula

    Well, I added a column to show From to To on the range, then in J3 I entered this formula:
    =COUNTIFS($D$2:$D$21,">="&G3,$D$2:$D$21,"<="&H3) + COUNTIFS($D$2:$D$21,">="&G3,$D$2:$D$21,"<="&H3,$E$2:$E$21,"SPLIT")
    and copy it down, but in J12 I put in this formula:
    =COUNTIFS($D$2:$D$21,">="&G12) + COUNTIFS($D$2:$D$21,">="&G12,$E$2:$E$21,"SPLIT")
    Please see attached.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    02-20-2022
    Location
    Indonesia
    MS-Off Ver
    Excel 2016
    Posts
    103

    Re: Trying to tolve my problem with COUNTIFS or other formula

    This is work. Thank you Sir

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,860

    Re: Trying to tolve my problem with COUNTIFS or other formula

    You're welcome, and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Countifs formula problem
    By crackerjack- in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-23-2017, 12:01 AM
  2. Countifs formula problem
    By mkoski in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-06-2016, 04:08 PM
  3. COUNTIFS Formula Problem
    By Archangel30IL in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-05-2014, 01:21 PM
  4. [SOLVED] Problem with COUNTIFS formula
    By BeerKing in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2014, 10:19 AM
  5. [SOLVED] problem in countifs formula
    By hello911helpme in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-17-2013, 08:14 AM
  6. Countifs formula problem
    By amberj32 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2012, 11:22 AM
  7. COUNTIFS + AND formula problem
    By Kagesen in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-25-2012, 03:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1