Countifs

[philb99]

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]

I philb99,

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

 

[philb99]

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]

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:

Book1
	H	P	AL	AN
1	Text	Region	Status	Aged
2	A - High","B - Associate - Hi	North	WIP	111
3	A - High","B - Associate - Hi	North	WIP	107
4	A - High","B - Associate - Hi	North	WIP	36
5	A - High","B - Associate - Hi	North	WIP	56
6	A - High","B - Associate - Hi	North	Something else	97
7	A - High","B - Associate - Hi	North	Something else	45
8	A - High","B - Associate - Hi	North	Something else	116
9	A - High","B - Associate - Hi	North	WIP	102
10	A - High","B - Associate - Hi	Something else	WIP	33
11	A - High","B - Associate - Hi	Something else	WIP	54
12	A - High","B - Associate - Hi	Something else	WIP	54
13	A - High","B - Associate - Hi	North	WIP	0
14	Something else	North	WIP	56
15	Something else	North	WIP	14
16	Something else	North	WIP	46
17	Something else	North	Something else	72
18	Something else	North	Something else	50
19	Something else	North	Something else	105
DataWIP

Data and Formula in Main Tab:

Book1
	A	B	C
1	Age	Days	Count
2	Aged 0	0	1
3	Aged 60	60	2
4	Aged 110	110	2
5	Aged>110		1
Main

Cell Formulas
Range	Formula
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)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[philb99]

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]

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:

Book1
	H	P	AL	AN
1	Text	Region	Status	Aged
2	A - High	North	WIP	111
3	A - High	North	WIP	107
4	A - High	North	WIP	36
5	B - Associate - Hi	North	WIP	56
6	B - Associate - Hi	North	Something else	97
7	B - Associate - Hi	North	Something else	45
8	A - High	North	Something else	116
9	B - Associate - Hi	North	WIP	102
10	B - Associate - Hi	Something else	WIP	33
11	A - High	Something else	WIP	54
12	A - High	Something else	WIP	54
13	A - High	North	WIP	0
14	Something else	North	WIP	56
15	Something else	North	WIP	14
16	Something else	North	WIP	46
17	B - Associate - Hi	North	WIP	72
18	Something else	North	Something else	50
19	Something else	North	Something else	105
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.

Book1
	A	B	C
1	Age	Days	Count
2	Aged 0	0	1
3	Aged 60	60	2
4	Aged 110	110	3
5	Aged>110		1
Main (2)

Cell Formulas
Range	Formula
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)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[philb99]

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

 

[Aryatect]

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]

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