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".

cawill · 10 Mar 2016 at 16:02

You could use an array formula instead.

For example enter this formula and press Ctrl + Shift + Enter:

=SUM((LEFT(A1:A9,4)="test")*(C1:C9="XYZ"))

Change the range references and criteria as needed.

dirtychinchilla · 10 Mar 2016 at 16:18

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.

estebanrey · 10 Mar 2016 at 16:32

Here you go buddy...

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

..looking at...

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?

Gaverick · 10 Mar 2016 at 19:34

You can't use the left formula on a column like that, it works on single cells at a time, unless you put it in to an array formula.

When you are using "text" as your criteria, are you saying the cell will actually contain the word text somewhere within, or are you trying to say count if the data in this cell is of the text type?

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.