Count occurences over range if a certain criteria met

[Richhall]

Hi

I have a named range, called Servers (B2:I50). Within this range I
want to count how many entries begin with New, so figure I need to use
Left somehow, but am not sure how I can use this in a single cell with
some countif/sumproduct function to count them all. Can anyone help
please?



B C

New1 Server C
Server A new3
Server B Server E
Server C Server F
New2 Server G
Server B Server D
Server A New 4


So this would return 4.


Cheers

Rich

 

[Bill Kuunders]

=COUNTIF(B2:I50,"new*")

note the * after new
Greetings from New Zealand

 

[Ms-Exl-Learner]

=COUNTIF(B:C,"NEW*")

Remember to Click Yes, if this post helps!

 

[Richhall]

Excellent thank you, how do I count Unique entries please? Request has
moved on a notch!

i.e

Server A New1
New 1 New 2
Server B new 3
new 2 new 1

Would return 3.

Thank you

Rich

 

[Ms-Exl-Learner]

Assume that your data is starts From A1 cell and end with B4 cell.

A Col B Col
Row 1 Server A New1
Row 2 New 1 New 2
Row 3 Server B new 3
Row 4 new 2 new 1

Copy the below formula and place the cursor in any cell other than A1 to B4
cell and press F2 and give Cntrl+V and Press Cntrl+Shift+Enter since it is an
array formula.
=SUM(ISNUMBER(FIND("New",A1:B10))+0)

After pressing Cntrl+Shift+Enter the formula will be covered with Curly
Braces like the below in the Formulabar.

{=SUM(ISNUMBER(FIND("New",A1:B10))+0)}

Don’t type the Curly Braces Manually.

 

[Richhall]

thank you, and Happy Chistmas.

 

[Ms-Exl-Learner]

Thank you and you are welcome

Remember to Click Yes, if this post helps!