Countifs

philb99

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

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
295
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
295
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
295
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,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top