COUNTIF, counting one word with different combinations

[kochan122]

I am not sure if I will be able to describe my situation correctly but I will try.

So I am making report based on reports from 2006-2018. In each year I have columns with data like this:[TABLE="width: 278"]
<tbody>[TR]
[TD="class: xl66, width: 91, align: center"]2007[/TD]
[TD="class: xl65, width: 187, align: center"]Recycle[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 91, align: center"]2007[/TD]
[TD="class: xl65, width: 187, align: center"]Reuse; Recycle[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 91, align: center"]2007[/TD]
[TD="class: xl65, width: 187, align: center"]Reuse; Reduce[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 91, align: center"]2008[/TD]
[TD="class: xl65, width: 187, align: center"]Reduce; Recycle[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 91, align: center"]2007[/TD]
[TD="class: xl65, width: 187, align: center"]Recycle[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 91, align: center"]2007[/TD]
[TD="class: xl65, width: 187, align: center"]Recycle[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 91, align: center"]2009[/TD]
[TD="class: xl65, width: 187, align: center"]Reuse; Reduce; Recycle[/TD]
[/TR]
</tbody>[/TABLE]
I was just writing data like this in one column, without any multi-select or any other option.
Now I want to count for each year how many there were Reuse, then Reduce and Recycle, so I was trying to use
=COUNTIF(B1:B7;"Reuse"), and I thought it would filter whole column and count each time there is Reuse mentioned. Additionally, I was just selecting each year separately so I could use this command with small change of range. But unfortunately it didn't work. This formula only counted the one with only Reuse, didn't count Reuse; Reduce etc.

Do you have any advice how could I do it so for example I can in criteria write maybe all option with Reuse in it and then it will count every one with Reuse in it?

 

[kochan122]

it would be something like this for example

[TABLE="width: 394"]
<tbody>[TR]
[TD="class: xl64, width: 87, align: right"]2007[/TD]
[TD="class: xl64, width: 171, align: left"]Recycle[/TD]
[TD="class: xl64, width: 136, align: left"]Manufacture[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2007[/TD]
[TD="class: xl64, align: left"]Reuse; Recycle[/TD]
[TD="class: xl64, align: left"]Manufacture[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2007[/TD]
[TD="class: xl64, align: left"]Reuse; Reduce[/TD]
[TD="class: xl64, align: left"]Construction[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2008[/TD]
[TD="class: xl64, align: left"]Reduce; Recycle[/TD]
[TD="class: xl64, align: left"]Manufacture[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2007[/TD]
[TD="class: xl64, align: left"]Recycle[/TD]
[TD="class: xl64, align: left"]Information[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2007[/TD]
[TD="class: xl64, align: left"]Recycle[/TD]
[TD="class: xl64, align: left"]Construction[/TD]
[/TR]
[TR]
[TD="class: xl64, align: right"]2009[/TD]
[TD="class: xl64, align: left"]Reduce; Recycle[/TD]
[TD="class: xl64, align: left"]Manufacture[/TD]
[/TR]
</tbody>[/TABLE]

 

[GR00007]

The example given at 9:47 was intentionally without a sum of values. I thought that you might have other data points you'd want to use in calculation, so the method referenced would allow pivot table process, slice and dicing any way you want.

 

[AlanY]

I want to ask additionally, because I will do it later on and maybe the formula need just small alternation

If I add another column with industries, so for example A are years, B is Reuse... and C would be for example Manufacture, Construction and Information.
And I want to have every year each of this industry have how much of Reuse then Reduce then Recycle separately. For example 2007 Manufacture has 3 Recycle, 3 Reuse and 2 Reuse, then 2007 Construction and so on.

you can just extend the formula like this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1					Manufacture			Construction			Information
2	2007	Recycle	Manufacture		Reuse	Reduce	Recycle	Reuse	Reduce	Recycle	Reuse	Reduce	Recycle
3	2007	Reuse; Recycle	Manufacture	2007	1	0	2	1	1	1	0	0	1
4	2007	Reuse; Reduce	Construction	2008	0	1	1	0	0	0	0	0	0
5	2008	Reduce; Recycle	Manufacture	2009	0	1	1	0	0	0	0	0	0
6	2007	Recycle	Information
7	2007	Recycle	Construction
8	2009	Reduce; Recycle	Manufacture
Sheet7

Cell Formulas
Range	Formula
E3	=SUMPRODUCT(--($A$2:$A$8=$D3),--($C$2:$C$8=$E$1),--ISNUMBER(SEARCH(E$2,$B$2:$B$8)))
H3	=SUMPRODUCT(--($A$2:$A$8=$D3),--($C$2:$C$8=$H$1),--ISNUMBER(SEARCH(H$2,$B$2:$B$8)))
K3	=SUMPRODUCT(--($A$2:$A$8=$D3),--($C$2:$C$8=$K$1),--ISNUMBER(SEARCH(K$2,$B$2:$B$8)))