countifs
• Home
• Courses
• Promotions
• Schedule
• Formats
• Our Clients

# COUNTIFS

Resolved · Medium Priority · Version 2019

Tue 20 Aug 2019, 17:13Delegate 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!

For upcoming training course dates see:

Tue 20 Aug 2019, 18:35Trainer 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: 0845 5194 797
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:43Delegate 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:45Delegate 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:05Trainer 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: 0845 5194 797
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

Training information:

 Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions What does 'Resolved' mean? Any suggestions, questions or comments? Please post in the Improve the forum thread.

### Viewing two Excel 2010 Workbooks at the same time

Did you know you can view two Excel 2010 workbooks side by side? Very useful when comparing data without constantly having to go back and forth!

1) Open both Excel workbooks
2) Select Window then select Compare Side by Side with (Spreadsheet 2)
3) When you have finished, select Window again and click Close Side by Side

View all Excel hints and tips

TrustScore 4.8 / 5       1212 reviews