Results 1 to 9 of 9

Thread: Countifs
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Countifs

    Just need some help with an additional ask.

    I have the following formulae, which provides aged analysis on all of my data. There are further ages (this is a sample)

    SUM(COUNTIFS(DataWIP!$H:$H,{"A- High","B - Associate - Hi"},DataWIP!$AL:$AL,"WIP",DataWIP!AN:AN,"<="&0))
    SUM(COUNTIFS(DataWIP!$H:$H,{"A - High","B - Associate - Hi"},DataWIP!$AL:$AL,"WIP",DataWIP!AN:AN,">"&0,DataWIP!AN:AN,"<="&60))
    SUM(COUNTIFS(DataWIP!$H:$H,{"A - High","B - Associate - Hi"},DataWIP!$AL:$AL,"WIP",DataWIP!AN:AN,">="&61,DataWIP!AN:AN,"<="&110))

    There are 5 different Regions recorded under column P in my data

    I would like to provide the aged analysis for 1 Region only

    Thanks

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Countifs

    I philb99,

    Will it be possible to share some dummy data for DataWIP tab also.

  3. #3
    Board Regular
    Join Date
    Feb 2014
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs

    DataWIP as you know is the name of the tab

    Col H Looking for A- High","B - Associate - Hi
    Col AL Looks for WIP
    Col AN Looks for the aged analysis IE 1-60 Days, 61-110 and so on
    Column P - Regions are North South West and North - I want to look for North only

  4. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Countifs

    Hi philb99,

    I have tried to create the logic in the single sheet, see if this works, i have used FREQUENCY function here, so you will need to select 1 more than the bucket as in this case I had selected C2:C5 and then Press CTRL+SHIFT+ENTER

    Let me know if it works.

    Data in DataWIP tab:

    HPALAN
    1TextRegionStatusAged
    2A - High","B - Associate - HiNorthWIP111
    3A - High","B - Associate - HiNorthWIP107
    4A - High","B - Associate - HiNorthWIP36
    5A - High","B - Associate - HiNorthWIP56
    6A - High","B - Associate - HiNorthSomething else97
    7A - High","B - Associate - HiNorthSomething else45
    8A - High","B - Associate - HiNorthSomething else116
    9A - High","B - Associate - HiNorthWIP102
    10A - High","B - Associate - HiSomething elseWIP33
    11A - High","B - Associate - HiSomething elseWIP54
    12A - High","B - Associate - HiSomething elseWIP54
    13A - High","B - Associate - HiNorthWIP0
    14Something elseNorthWIP56
    15Something elseNorthWIP14
    16Something elseNorthWIP46
    17Something elseNorthSomething else72
    18Something elseNorthSomething else50
    19Something elseNorthSomething else105

    DataWIP





    Data and Formula in Main Tab:

    ABC
    1AgeDaysCount
    2Aged 001
    3Aged 60602
    4Aged 1101102
    5Aged>1101

    Main



    Array Formulas
    CellFormula
    C2:C5{=FREQUENCY(IFERROR(IF(DataWIP!$H$2:$H$19="A - High"",""B - Associate - Hi",TRUE,1/0)*IF(DataWIP!$P$2:$P$19="North",TRUE,1/0)*IF(DataWIP!$AL$2:$AL$19="WIP",TRUE,1/0)*(DataWIP!$AN$2:$AN$19),""),$B$2:$B$5)}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


  5. #5
    Board Regular
    Join Date
    Feb 2014
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs

    Thanks for your support - but i hope you can help me understand.

    In my raw data Column H has descriptions and I am looking for the ones highlighted.

    My own results tables is set out as per your Col A above Col B has the relevant formulae I highlighted in the first post and counts all Regions and Col C will be your formulae where I am looking for just NORTH Region.

    I am not getting any results from your formulae even if I change the raw data. Can you let me know why would you enter $B$2:$B$5 at the end

  6. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Countifs

    Hi,

    So FREQUENCY function finds the values from the Data and puts in the specified buckets, so $B$2:$B$5 from the data I have given above provides the buckets, which for you were 0, 1 to 60, 61 to 110. The extra cell ($B$5) gives any value greater that 110 value.

    I changed the value used in Column H with this ("A - High" & "B - Associate - Hi"), sorry I just copied the whole thing .



    DataWIP Tab now:

    HPALAN
    1TextRegionStatusAged
    2A - HighNorthWIP111
    3A - HighNorthWIP107
    4A - HighNorthWIP36
    5B - Associate - HiNorthWIP56
    6B - Associate - HiNorthSomething else97
    7B - Associate - HiNorthSomething else45
    8A - HighNorthSomething else116
    9B - Associate - HiNorthWIP102
    10B - Associate - HiSomething elseWIP33
    11A - HighSomething elseWIP54
    12A - HighSomething elseWIP54
    13A - HighNorthWIP0
    14Something elseNorthWIP56
    15Something elseNorthWIP14
    16Something elseNorthWIP46
    17B - Associate - HiNorthWIP72
    18Something elseNorthSomething else50
    19Something elseNorthSomething else105

    DataWIP





    For your Sheet: If you want you can move the bucket values Column A and B from below to somewhere else and refer Column C formula's last argument ($B2:$B5) to refer to wherever you keeping the bucket list.

    ABC
    1AgeDaysCount
    2Aged 001
    3Aged 60602
    4Aged 1101103
    5Aged>1101

    Main (2)



    Array Formulas
    CellFormula
    C2:C5{=FREQUENCY(IFERROR(IF(DataWIP!$H$2:$H$19={"A - High","B - Associate - Hi"},TRUE,1/0)*IF(DataWIP!$P$2:$P$19="North",TRUE,1/0)*IF(DataWIP!$AL$2:$AL$19="WIP",TRUE,1/0)*(DataWIP!$AN$2:$AN$19),""),$B$2:$B$5)}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


  7. #7
    Board Regular
    Join Date
    Feb 2014
    Posts
    295
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Countifs

    Thanks for your help but all I am getting is 1's all the way down - hoping you can help me

  8. #8
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Countifs

    Hi,

    While entering the formula from the example from #6 , make sure that you have columns C2:C5 selected, then enter the formula and press CTRL+SHIFT+ENTER. If you select only C2 and enter the formula and copy it down it will only show you 1 which is count of "Aged 0"
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  9. #9
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Countifs

    Also, you can use below it does the same thing but is a little smaller:

    {=FREQUENCY(IF((DataWIP!$P$2:$P$19="North")*(DataWIP!$AL$2:$AL$19="WIP")*((DataWIP!$H$2:$H$19="A - High")+(DataWIP!$H$2:$H$19="B - Associate - Hi")),DataWIP!$AN$2:$AN$19),$B$2:$B$5)}
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

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
  •