Excel COUNTIFS help

bazzabear · 10 Mar 2016 at 15:06

I'm trying to get a nested IF/COUNTIFS formula to work, but I'm not sure if I'm asking for something it can't do.

The last COUNTIF should be checking for a piece of text - but it's not checking the whole cell, just the first few characters from the left. Can you combine COUNTIF with LEFT? I've got something like this:

=COUNTIFS('tab 2'!A:A,C4,LEFT('tab 2'!F:F,4),"text")

It isn't working - is there a way to make it do so?

So it's supposed to look at the first column for a piece of text in C4, then look in column F to see if the first 4 characters are "text".

bazzabear · 10 Mar 2016 at 16:50

dirtychinchilla said:
The reason why it's only checking the first characters is the LEFT() command. It takes text from the left to however many characters you say.

I know what LEFT does, and that's what I want it to do, but it isn't, just states an error. I wonder whether this is because it's not even possible to nest a LEFT like this.

bazzabear · 10 Mar 2016 at 16:51

estebanrey said:
Here you go buddy...

=COUNTIFS('tab 2'!A:A,C4,'tab 2'!F:F,"text" & "*")

..looking at...

Aha! So it's possible to use wildcards? Thanks very much, I think that'll work fine.

Would just having "text*" work as well do you know?

bazzabear · 15 Mar 2016 at 15:32

Sorry, haven't been able to re-visit this until now. I was meaning the cell literally has (in fact starts with) the word "text".

I will now attempt to use estebanrey's solution, wish me luck!

bazzabear · 23 Mar 2016 at 13:40

bazzabear said:
Aha! So it's possible to use wildcards? Thanks very much, I think that'll work fine.

Would just having "text*" work as well do you know?

In case someone refers back to this in the distant future (probably likely to be me again!) I can confirm that:

"text*"

works exactly the same as:

"text" & "*"

Thanks again for the help.