Countifs

philb99

Active Member
Joined
Feb 3, 2014
Messages
299
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
I philb99,

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

philb99

Active Member
Joined
Feb 3, 2014
Messages
299
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
Joined
Jun 17, 2019
Messages
308
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
Joined
Feb 3, 2014
Messages
299
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
Joined
Jun 17, 2019
Messages
308
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
Joined
Feb 3, 2014
Messages
299
Thanks for your help but all I am getting is 1's all the way down - hoping you can help me
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
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
Joined
Jun 17, 2019
Messages
308
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)}
 

Forum statistics

Threads
1,089,422
Messages
5,408,145
Members
403,186
Latest member
123hpeinstall

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top