# 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,081,931
Messages
5,362,195
Members
400,671
Latest member
Tommy00836

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...