Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

countifs

ResolvedVersion 2019

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

Wed 28 Aug 2019: Automatically marked as resolved.

Excel tip:

##### displaying in Excel

When you get a series of hash symbols (####) appearing in some of your cells in a spreadsheet, this can make you think that you've make some kind of mistake.

This is a common misconception - what this actually means is that the cell is not wide enough to fully display the content of the cell.

All you need to do to see what is actually in the cell is to widen the column that the cell is in.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.