Help with Countifs

[Schubby10]

Hye guys looking for some help with countifs

In column A I am looking to create a formula to concatenate the dept and the various metrics.

The countifs should count each job code that begins with 1- that falls in this category.

Dept are in B and Unique Identifier for job code in E.

I currently have a formula IFERROR(CONCATENATE(COUNTIFS(E$1:E4,LEFT(E4,2)="1-",B$1:B4,B4),"-",B4),"")
But it only returns 0's any idea why?

 

[mrshl9898]

Some sample data would be helpful.

I don't think LEFT(E4,2)="1-" will produce a result.

Try a helper column with the LEFT, then countif that column is 1-

 

[Schubby10]

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Code[/TD]
[TD="width: 64"]Dept[/TD]
[TD="width: 64"]Dept Code[/TD]
[TD="width: 64"]Dept Head[/TD]
[TD="width: 64"]Job Code[/TD]
[/TR]
[TR]
[TD]1-AR[/TD]
[TD]AR[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]1-123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AR[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]2-123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AR[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]3-123[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]AR[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]4-123[/TD]
[/TR]
[TR]
[TD]2-AR[/TD]
[TD]AR[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]1-677[/TD]
[/TR]
[TR]
[TD]3-AR[/TD]
[TD]AR[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]1-789[/TD]
[/TR]
[TR]
[TD]4-AR[/TD]
[TD]AR[/TD]
[TD][/TD]
[TD]Bob[/TD]
[TD]1-801[/TD]
[/TR]
[TR]
[TD]1-Retail[/TD]
[TD]Retail[/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]1-875[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]REtail[/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]2-875[/TD]
[/TR]
[TR]
[TD]2-Retail[/TD]
[TD]Retial[/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]1-982[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Retail[/TD]
[TD][/TD]
[TD]Joe[/TD]
[TD]2-982[/TD]
[/TR]
[TR]
[TD]1-Online[/TD]
[TD]Online[/TD]
[TD][/TD]
[TD]Amy[/TD]
[TD]1-654[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Online[/TD]
[TD][/TD]
[TD]Amy[/TD]
[TD]2-654[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

F2 - =LEFT(E2,2)
G2 - =COUNTIFS(F$1:F2,"1-",B$1:B2,B2)

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Code[/TD]
[TD="class: xl65, width: 64"]Dept[/TD]
[TD="class: xl65, width: 64"]Dept Code[/TD]
[TD="class: xl65, width: 64"]Dept Head[/TD]
[TD="class: xl65, width: 64"]Job Code[/TD]
[TD="class: xl65, width: 64"]left[/TD]
[TD="class: xl65, width: 64"]result[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]1-AR[/TD]
[TD="class: xl65, width: 64"]AR[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]1-123[/TD]
[TD="class: xl65, width: 64"]1-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]AR[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]2-123[/TD]
[TD="class: xl65, width: 64"]2-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]AR[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]3-123[/TD]
[TD="class: xl65, width: 64"]3-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]AR[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]4-123[/TD]
[TD="class: xl65, width: 64"]4-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]2-AR[/TD]
[TD="class: xl65, width: 64"]AR[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]1-677[/TD]
[TD="class: xl65, width: 64"]1-[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]3-AR[/TD]
[TD="class: xl65, width: 64"]AR[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]1-789[/TD]
[TD="class: xl65, width: 64"]1-[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]4-AR[/TD]
[TD="class: xl65, width: 64"]AR[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Bob[/TD]
[TD="class: xl65, width: 64"]1-801[/TD]
[TD="class: xl65, width: 64"]1-[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]1-Retail[/TD]
[TD="class: xl65, width: 64"]Retail[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Joe[/TD]
[TD="class: xl65, width: 64"]1-875[/TD]
[TD="class: xl65, width: 64"]1-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]REtail[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Joe[/TD]
[TD="class: xl65, width: 64"]2-875[/TD]
[TD="class: xl65, width: 64"]2-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]2-Retail[/TD]
[TD="class: xl65, width: 64"]Retial[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Joe[/TD]
[TD="class: xl65, width: 64"]1-982[/TD]
[TD="class: xl65, width: 64"]1-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Retail[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Joe[/TD]
[TD="class: xl65, width: 64"]2-982[/TD]
[TD="class: xl65, width: 64"]2-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]1-Online[/TD]
[TD="class: xl65, width: 64"]Online[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Amy[/TD]
[TD="class: xl65, width: 64"]1-654[/TD]
[TD="class: xl65, width: 64"]1-[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Online[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]Amy[/TD]
[TD="class: xl65, width: 64"]2-654[/TD]
[TD="class: xl65, width: 64"]2-[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Was having another look, and thought i'd add this for your future benefit.

The reason E$1:E4,LEFT(E4,2)="1-" was failing is because LEFT(E4,2)="1-" or = = will return a TRUE or FALSE depending if there's a match or not. So you were searching E$1:E4 for TRUE or FALSE.

 

[jtakw]

Hi,

Perhaps this is what you're after, formula in A2 copied down:

Book1
	A	B	C	D	E
1	Code	Dept	Dept Code	Dept Head	Job Code
2	1-AR	AR		Bob	1-123
3		AR		Bob	2-123
4		AR		Bob	3-123
5		AR		Bob	4-123
6	2-AR	AR		Bob	1-677
7	3-AR	AR		Bob	1-789
8	4-AR	AR		Bob	1-801
9	1-Retail	Retail		Joe	1-875
10		Retail		Joe	2-875
11	2-Retail	Retail		Joe	1-982
12		Retail		Joe	2-982
13	1-Online	Online		Amy	1-654
14		Online		Amy	2-654
Sheet383

Cell Formulas
Range	Formula
A2	=IF(LEFT(E2,2)="1-",COUNTIFS(B$2:B2,B2,E$2:E2,"1-*")&"-"&B2,"")