Count occurences over range if a certain criteria met

  • Thread starter Thread starter Richhall
  • Start date Start date
R

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
 
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
 
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.
 
Back
Top