COUNTIFS

Resolved · Medium Priority · Version 2019

Tue 20 Aug 2019, 17:13 Delegate Ben said...

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!

Tue 20 Aug 2019, 18:35 Trainer Jens said...

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

Wed 21 Aug 2019, 08:43 Delegate Ben said...

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?

Wed 21 Aug 2019, 08:45 Delegate Ben said...

RE: COUNTIFS

It could possibly be because the second criteria appears in a long string of text, which is why I used the wildcards (*).

Wed 21 Aug 2019, 09:05 Trainer Jens said...

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.

Training courses

	98.8 % recommend us
	More live stats...

Training information:

Excel tip:

Adding up time greater than 24 hours

When you add up time if it exceeds 24 hours i.e 27 hours appears as 03:00. Go to Format / Cells / Number / Custom. The format is hh:mm but if change it to [hh]:mm it will add up to the correct amount of hours.

View all Excel hints and tips