Count occurences over range if a certain criteria met
 

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

Count occurences over range if a certain criteria met
 

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

note the * after new
Greetings from New Zealand

"Richhall" wrote in message
...
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

Count occurences over range if a certain criteria met
 

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

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Richhall" wrote:

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
.

Count occurences over range if a certain criteria met
 

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

Count occurences over range if a certain criteria met
 

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)}

Dont type the Curly Braces Manually.

--------------------
(Ms-Exl-Learner)
--------------------


"Richhall" wrote:

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
.

Count occurences over range if a certain criteria met
 

thank you, and Happy Chistmas.

Count occurences over range if a certain criteria met
 

Thank you and you are welcome

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Richhall" wrote:

thank you, and Happy Chistmas.
.