count of words in multiple cell range

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,462

Office Version

1. 365

Platform

1. Windows

• Sep 27, 2014

• #2

Give this formula a try (change the ranges to match your actual data layout)...

=COUNTIFS(B2:B10,"ended",C2:C10,"Multiple",D2:D10,"last")

 

Upvote 0

J

Justyna82

New Member

Joined

Sep 1, 2014

Messages

18

• Sep 27, 2014

• #3

I tried this one already and it brings 0...I am not sure if maybe it needs AND?

 

Upvote 0

J

Justyna82

New Member

Joined

Sep 1, 2014

Messages

18

• Sep 27, 2014

• #4

I also tried countifs(b2:b10,"=ended",c2:c10,"=multiple", d2:d10,"last")

 

Upvote 0

J

Justyna82

New Member

Joined

Sep 1, 2014

Messages

18

• Sep 27, 2014

• #5

And I checked few times now, and I do have this kind of entries!!! Cant understand why this formula not bringing any results??

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,462

Office Version

1. 365

Platform

1. Windows

• Sep 27, 2014

• #6

Justyna82 said:

I tried this one already and it brings 0...I am not sure if maybe it needs AND?

Click to expand...

No, COUNTIF does not make use of logical operators. I will tell you that the formula worked on my system when I copy/pasted what you posted in Message #1 into cells A1:D10 on my worksheet, so I know the formula works. You might try doing that on a new worksheet to prove to yourself the formula works properly. Next, you should check your entries on your original data to make sure there are not spaces attached to the values you are checking. This formula will tell you quickly if you have spaces or not... if it works correctly (returns 3 for your example data), then you have spaces is at least one columns worth of entries.

=COUNTIFS(B2:B10,"*ended*",C2:C10,"*Multiple*",D2:D10,"*last*")

 

Upvote 0

J

Justyna82

New Member

Joined

Sep 1, 2014

Messages

18

• Sep 27, 2014

• #7

Could that be because behind the words there is different formula???

 

Upvote 0

Rick Rothstein

MrExcel MVP

Joined

Apr 18, 2011

Messages

38,462

Office Version

1. 365

Platform

1. Windows

• Sep 27, 2014

• #8

Justyna82 said:

Could that be because behind the words there is different formula???

Click to expand...

The formula should work whether the words are constants or the result of formulas. Did you try the formula I posted in Message #6? Did it work?

 

Upvote 0

J

Justyna82

New Member

Joined

Sep 1, 2014

Messages

18

• Sep 27, 2014

• #9

Customer	Multiple/ single
1	Single
2	Multiple	X
2	single
3	Multiple	X
3	Multiple
3	Multiple
4	Multiple	X
4	Multiple
4	Multiple

<tbody>
</tbody>

I actually noticed other error or rather mistake in my other formula, example of the current results below. i used this formula: =if(a2=a1, "multiple", if(a2=a3,"multiple","single")) However this formula brings incorrectly multiples and i am not sure why? Think something isnt right with the first part... really thanks for help!!!!!! i hope this make sense....

 

Upvote 0

J

Justyna82

New Member

Joined

Sep 1, 2014

Messages

18

• Sep 27, 2014

• #10

Idid, yes but than I noticed the error in the formula I posted recently. I think if that resolved I will achieve the desire outcome...

 

Upvote 0