countifs

TrustPilot

starstarstarstarstar Excellent

Forum home » Delegate support and help forum » Microsoft Excel Training and help » COUNTIFS

COUNTIFS

resolvedResolved · Medium Priority · Version 2019

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

For upcoming training course dates see: Pricing & availability

Tue 20 Aug 2019, 18:35 replyReply 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: 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:43 replyReply 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 replyReply 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 replyReply 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: 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


 

Excel tip:

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


TrustPilot
TrustScore 4.9 / 5       1094 reviews