# Thread: Countifs Thanks: 0 Likes: 0

1. ## Countifs

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

2. ## Re: Countifs

I philb99,

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

3. ## Re: Countifs

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

4. ## Re: Countifs

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:

HPALAN
1TextRegionStatusAged
2A - High","B - Associate - HiNorthWIP111
3A - High","B - Associate - HiNorthWIP107
4A - High","B - Associate - HiNorthWIP36
5A - High","B - Associate - HiNorthWIP56
6A - High","B - Associate - HiNorthSomething else97
7A - High","B - Associate - HiNorthSomething else45
8A - High","B - Associate - HiNorthSomething else116
9A - High","B - Associate - HiNorthWIP102
10A - High","B - Associate - HiSomething elseWIP33
11A - High","B - Associate - HiSomething elseWIP54
12A - High","B - Associate - HiSomething elseWIP54
13A - High","B - Associate - HiNorthWIP0
14Something elseNorthWIP56
15Something elseNorthWIP14
16Something elseNorthWIP46
17Something elseNorthSomething else72
18Something elseNorthSomething else50
19Something elseNorthSomething else105

DataWIP

Data and Formula in Main Tab:

ABC
1AgeDaysCount
2Aged 001
3Aged 60602
4Aged 1101102
5Aged>1101

Main

Array Formulas
CellFormula
C2:C5{=FREQUENCY(IFERROR(IF(DataWIP!\$H\$2:\$H\$19="A - High"",""B - Associate - Hi",TRUE,1/0)*IF(DataWIP!\$P\$2:\$P\$19="North",TRUE,1/0)*IF(DataWIP!\$AL\$2:\$AL\$19="WIP",TRUE,1/0)*(DataWIP!\$AN\$2:\$AN\$19),""),\$B\$2:\$B\$5)}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

5. ## Re: Countifs

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

6. ## Re: Countifs

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:

HPALAN
1TextRegionStatusAged
2A - HighNorthWIP111
3A - HighNorthWIP107
4A - HighNorthWIP36
5B - Associate - HiNorthWIP56
6B - Associate - HiNorthSomething else97
7B - Associate - HiNorthSomething else45
8A - HighNorthSomething else116
9B - Associate - HiNorthWIP102
10B - Associate - HiSomething elseWIP33
11A - HighSomething elseWIP54
12A - HighSomething elseWIP54
13A - HighNorthWIP0
14Something elseNorthWIP56
15Something elseNorthWIP14
16Something elseNorthWIP46
17B - Associate - HiNorthWIP72
18Something elseNorthSomething else50
19Something elseNorthSomething else105

DataWIP

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.

ABC
1AgeDaysCount
2Aged 001
3Aged 60602
4Aged 1101103
5Aged>1101

Main (2)

Array Formulas
CellFormula
C2:C5{=FREQUENCY(IFERROR(IF(DataWIP!\$H\$2:\$H\$19={"A - High","B - Associate - Hi"},TRUE,1/0)*IF(DataWIP!\$P\$2:\$P\$19="North",TRUE,1/0)*IF(DataWIP!\$AL\$2:\$AL\$19="WIP",TRUE,1/0)*(DataWIP!\$AN\$2:\$AN\$19),""),\$B\$2:\$B\$5)}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

7. ## Re: Countifs

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

8. ## Re: Countifs

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"

9. ## Re: Countifs

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•