# Countifs

#### philb99

##### Active Member

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
I philb99,

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

#### 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>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>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)}

1,078,541
Messages
5,341,061
Members
399,414
Latest member
EMW2159

### This Week's Hot Topics

• Problem with Radio Button's format control
I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
• Last Display on userform to a Listbox
[CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
• Rename and move files to a new location
Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
• Help with True/False Formula
Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
• Clear extra characters from a provided range of cells
Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
• Help with Current and highest streaks
Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...