Ben has attended:
Excel Intermediate course
COUNTIFS
Hi,
I am currently using countifs like the following to count how many times text appears in cells (each with a lot of text in):
=COUNTIFS($I$2:$I$356,"Timber",$L$2:$L$356,"*Commitment to avoiding deforestation and forest degradation*")
However, I am hoping that it is possible to use cell references for the first criteria rather than manually typing in "timber" or "palm oil" etc for a long range of cells., and cell references for the second criteria "*commitment to avoiding deforestation and forest degradation*". These criteria vary across the worksheet.
So my question is how do I use a reference to a cell containing text for criteria1 and criteria2 in my countifs function? Currently if I type in the cell reference it returns an error.
Sorry if this is unclear, thanks!
RE: COUNTIFS
Hi Ben,
Thank you for the forum question.
If you just type Timber,*commitment to avoiding deforestation and forest degradation*, and palm oil in different cells you shouldn't get an error by reference the cells.
If you have Timber in lets say K10 and *commitment to avoiding deforestation and forest degradation* in K11 the formula should look like this.
=COUNTIFS($I$2:$I$356,K10,$L$2:$L$356,K11)
The function will just return 0 if it cannot find the criteria and not an error.
If you want me to have a look at your sheet please send it to:
info@stl-training.co.uk
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: COUNTIFS
Thanks for your reply. Sorry I should have said that the function returns 0 rather than error. So for example if I use =COUNTIFS($I$2:$I$356,P5,$L$2:$L$356,V1) where p5 is a cell saying soy and v1 is a cell saying "commitment to innovation" it returns 0. But if I type in the criteria manually =COUNTIFS($I$2:$I$356,"Soy",$L$2:$L$356,"*Commitment to innovation*") it returns 6 (which is correct). Any ideas?
RE: COUNTIFS
It could possibly be because the second criteria appears in a long string of text, which is why I used the wildcards (*).
RE: COUNTIFS
Hi Ben,
If the countifs returns 0, then it is because it cannot find the criteria.
Lets say that you have typed the criteria *Commitment to innovation* in cell A3 and you have *Commitment to innovation* in your list in D10:
If you in a blank cell type =Exact(A3,D10). If the Exact function return True you have an exact match. If the function return False you have two different text string and you have the reason, why your Countifs return 0.
I hope this makes sense.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
