Countif()

[Finance Guru]

Hi There,

I am using Excel 2007.
Could someone please assist in the syntax for the following.
I want to count the number of occurrences in a row based on the value of
another cell in the same row. eg.

B2 = BACS
C2 to X2 = can contain various values
Count the occurrences in C2 to X2 where B2 = BACS

Sumif doesn't do it for me.

It's simple but for me it's a small problem

Many thanks in anticipation of a response
Finance Guru

 

[Mike H]

maybe

=COUNTIF(C2:X2,B2)

Mike

 

[Finance Guru]

Hi Mike,
No that doesn't do it
No matter how many entries in the row,the sum of Countif is 0

 

[Mike H]

Hi,

It does do it and if you're getting 0 then the data (BACS) in B2 is not the
same as any instances of BACS in C2 - X2. The most common cause is rogue
spaces

Mike

 

[Finance Guru]

Thanks for the reply. I think you misunderstood what I was trying to achieve.

B2 C2 D2 E2 F2
BACS 100.00 150.00 500.00 99.99 = the Occurences = 4 because B2 has
BACS within

or alternatively if replace B2 within the row.

Empty 100.00 150.00 500.00 99.99 = the Occurences = 0 because B2 has
nothing in it.

However the good news is that I have solved the problem by simply putting in
an IF
statement,like IF(B2="BACS"(COUNT(C2:F2),0)) and it works.

Out of sheer interest if you can think of COUNTIF() that does the job,i
would welcome it.

In the meantime many thanks for taking the time out to respond and have an
enjoyable Bank Holiday if you are resident in UK ( weather permitting ).

FinanceGuru