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?

 

[AlanY]

try this

Book1
	A	B	C	D	E	F
1				Reuse	Reduce	Recycle
2	2007	Recycle	2007	2	1	4
3	2007	Reuse; Recycle	2008	0	1	1
4	2007	Reuse; Reduce	2009	0	1	1
5	2008	Reduce; Recycle
6	2007	Recycle
7	2007	Recycle
8	2009	Reduce; Recycle
Sheet7

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--($A$2:$A$8=$C2),--ISNUMBER(SEARCH(D$1,$B$2:$B$8)))

 

[GR00007]

Here's another alternative that you might want to consider:
[TABLE="class: grid, width: 349"]
<tbody>[TR]
[TD]Year[/TD]
[TD]Data[/TD]
[TD]Reuse[/TD]
[TD]Reduce[/TD]
[TD]Recycle[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]Recycle[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]Reuse; Recycle[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]Reuse; Reduce[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]2008[/TD]
[TD]Reduce; Recycle[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]Recycle[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]2007[/TD]
[TD]Recycle[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]2009[/TD]
[TD]Reuse; Reduce; Recycle[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

Columns C2 thru E2 have the following formulas which were then copied down for all rows:

=IFERROR(IF(FIND("Reuse",B2)>0,1,0),0)
=IFERROR(IF(FIND("Reduce",B2)>0,1,0),0)
=IFERROR(IF(FIND("Recycle",B2)>0,1,0),0)

 

[mikerickson]

COUNTIF will take wild cards
=COUNTIF(B1:B7, "Resuse") will return the number of cells that have exactly "Reuse" (case insensitive)

=COUNTIF(B1:B7, "*Reuse*") will return the number of celss that contain "Reuse" somewhere in the cell.

If you want to restrict it to 2007, I would use

=COUNTIFS(A1:A7, 2007, B1:B7, "*Reuse*")

 

[kochan122]

I tried this, even on new sheet to get the exact same situation to try it out.
=SUMPRODUCT(--($A$2:$A$8=$C2),--ISNUMBER(SEARCH(D1,B2:B8)))

but there seems to be some problem with the second part, I don't have the option to choose D1 for example or the array B2:B8.
I was only able to choose everything till here =SUMPRODUCT(--($A$2:$A$8=$C2), then I had to write it by myself but there is communicate that there is a problem with the formula.

 

[AlanY]

I tried this, even on new sheet to get the exact same situation to try it out.
=SUMPRODUCT(--($A$2:$A$8=$C2),--ISNUMBER(SEARCH(D1,B2:B8)))

but there seems to be some problem with the second part, I don't have the option to choose D1 for example or the array B2:B8.
I was only able to choose everything till here =SUMPRODUCT(--($A$2:$A$8=$C2), then I had to write it by myself but there is communicate that there is a problem with the formula.

in order to copy the formula from D2, down and across you need to set (D1,B2:B8) as absolute like this

=SUMPRODUCT(--($A$2:$A$8=$C2),--ISNUMBER(SEARCH([COLOR="#FF0000"]D$1,$B$2:$B$8[/COLOR])))

and the formula in F4 should looks like

Book1
	A	B	C	D	E	F
1				Reuse	Reduce	Recycle
2	2007	Recycle	2007	`	1	4
3	2007	Reuse; Recycle	2008	0	1	1
4	2007	Reuse; Reduce	2009	0	1	1
5	2008	Reduce; Recycle
6	2007	Recycle
7	2007	Recycle
8	2009	Reduce; Recycle
Sheet7

Cell Formulas
Range	Formula
F4	=SUMPRODUCT(--($A$2:$A$8=$C4),--ISNUMBER(SEARCH(F$1,$B$2:$B$8)))

 

[kochan122]

in order to copy the formula from D2, down and across you need to set (D1,B2:B8) as absolute like this

=SUMPRODUCT(--($A$2:$A$8=$C2),--ISNUMBER(SEARCH([COLOR=#FF0000]D$1,$B$2:$B$8[/COLOR])))

and the formula in F4 should looks like

	A	B	C	D	E	F
Reuse	Reduce	Recycle
Recycle
Reuse; Recycle
Reuse; Reduce
Reduce; Recycle
Recycle
Recycle
Reduce; Recycle

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2007[/TD]

[TD="align: right"]2007[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]`[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2007[/TD]

[TD="align: right"]2008[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2007[/TD]

[TD="align: right"]2009[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2008[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2007[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2007[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2009[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]=SUMPRODUCT(--($A$2:$A$8=$C4),--ISNUMBER(SEARCH(F$1,$B$2:$B$8)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes, it worked! It was very smooth. I discovered that I needed to change "," to ";". I am really sorry if it is obvious, but I thought that coma will work.
Or maybe because I am working on MacoS that the coma is not working, not sure.

 

[kochan122]

And thanks to other users, I checked them and they also worked just fine.
Thanks to this I am able now to learn a lot of new things about excel !

 

[AlanY]

Yes, it worked! It was very smooth. I discovered that I needed to change "," to ";". I am really sorry if it is obvious, but I thought that coma will work.
Or maybe because I am working on MacoS that the coma is not working, not sure.

glad that works for you, as regarding the ' and ; , it's just different regional settings.
i missed that from your first post

 

[kochan122]

glad that works for you, as regarding the ' and ; , it's just different regional settings.
i missed that from your first post

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.