Countifs

philb99

Active Member
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
 

philb99

Active Member
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
 

Aryatect

Active Member
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>H</th><th>P</th><th>AL</th><th>AN</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Text</td><td style=";">Region</td><td style=";">Status</td><td style=";">Aged</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">111</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">107</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">36</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">56</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">97</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">116</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">102</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">A - High","B - Associate - Hi</td><td style=";">Something else</td><td style=";">WIP</td><td style="text-align: right;;">33</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">A - High","B - Associate - Hi</td><td style=";">Something else</td><td style=";">WIP</td><td style="text-align: right;;">54</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">A - High","B - Associate - Hi</td><td style=";">Something else</td><td style=";">WIP</td><td style="text-align: right;;">54</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">A - High","B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Something else</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">56</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Something else</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Something else</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">46</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Something else</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">72</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Something else</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Something else</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">105</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">DataWIP</p><br /><br />

Data and Formula in Main Tab:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Age</td><td style=";">Days</td><td style=";">Count</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Aged 0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Aged 60</td><td style="text-align: right;;">60</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Aged 110</td><td style="text-align: right;;">110</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Aged>110</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Main</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2:C5</th><td style="text-align:left">{=FREQUENCY(<font color="Blue">IFERROR(<font color="Red">IF(<font color="Green">DataWIP!$H$2:$H$19="A - High"",""B - Associate - Hi",TRUE,1/0</font>)*IF(<font color="Green">DataWIP!$P$2:$P$19="North",TRUE,1/0</font>)*IF(<font color="Green">DataWIP!$AL$2:$AL$19="WIP",TRUE,1/0</font>)*(<font color="Green">DataWIP!$AN$2:$AN$19</font>),""</font>),$B$2:$B$5</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

philb99

Active Member
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
 

Aryatect

Active Member
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>H</th><th>P</th><th>AL</th><th>AN</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Text</td><td style=";">Region</td><td style=";">Status</td><td style=";">Aged</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">A - High</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">111</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">A - High</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">107</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">A - High</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">36</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">56</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">B - Associate - Hi</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">97</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">B - Associate - Hi</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">45</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">A - High</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">116</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">102</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">B - Associate - Hi</td><td style=";">Something else</td><td style=";">WIP</td><td style="text-align: right;;">33</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">A - High</td><td style=";">Something else</td><td style=";">WIP</td><td style="text-align: right;;">54</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">A - High</td><td style=";">Something else</td><td style=";">WIP</td><td style="text-align: right;;">54</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">A - High</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Something else</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">56</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Something else</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Something else</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">46</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">B - Associate - Hi</td><td style=";">North</td><td style=";">WIP</td><td style="text-align: right;;">72</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Something else</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Something else</td><td style=";">North</td><td style=";">Something else</td><td style="text-align: right;;">105</td></tr></tbody></table><p style="width:5.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">DataWIP</p><br /><br />

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.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Age</td><td style=";">Days</td><td style=";">Count</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Aged 0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Aged 60</td><td style="text-align: right;;">60</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Aged 110</td><td style="text-align: right;;">110</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Aged>110</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Main (2)</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2:C5</th><td style="text-align:left">{=FREQUENCY(<font color="Blue">IFERROR(<font color="Red">IF(<font color="Green">DataWIP!$H$2:$H$19={"A - High","B - Associate - Hi"},TRUE,1/0</font>)*IF(<font color="Green">DataWIP!$P$2:$P$19="North",TRUE,1/0</font>)*IF(<font color="Green">DataWIP!$AL$2:$AL$19="WIP",TRUE,1/0</font>)*(<font color="Green">DataWIP!$AN$2:$AN$19</font>),""</font>),$B$2:$B$5</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

philb99

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

Aryatect

Active Member
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"
 

Aryatect

Active Member
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)}
 

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top