COUNTIF with criteria in 2 columns
 

I have the following...
CUSTOMER CODE
Jones A
Jones B
Jones B
Smith A
Smith C
Smith A
Jones B
Davis B

I need to be able to count how many cells ion the CODE column contain 'B',
but only if the CUSTOMER is 'Jones'

I cannot sort, or use named ranges in this case...

I'm sure its really straightforward, but I just can't work it out!

COUNTIF with criteria in 2 columns
 

=SUMPRODUCT(--(A2:A20="Jones"),--(B2:B20="B"))

SP cannot use whole columns, you have to define the range to use.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"EJ" wrote in message
...
I have the following...
CUSTOMER CODE
Jones A
Jones B
Jones B
Smith A
Smith C
Smith A
Jones B
Davis B

I need to be able to count how many cells ion the CODE column contain 'B',
but only if the CUSTOMER is 'Jones'

I cannot sort, or use named ranges in this case...

I'm sure its really straightforward, but I just can't work it out!

COUNTIF with criteria in 2 columns
 

Ignore me.... I've worked it out with SUMPRODUCT!

What I do need to know is a similar thing.

I've been using =SUMIF(C$5:C$317,"0.5") - column C can contain any value
between 1 and 50 and I need the sum, but I only need it to use this if cells
in column B meet a particular criteria, as per my previous question.


Thanks

COUNTIF with criteria in 2 columns
 

=SUMIF(B$5:B$317,"Jones",C$5:C$317)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"EJ" wrote in message
...
Ignore me.... I've worked it out with SUMPRODUCT!

What I do need to know is a similar thing.

I've been using =SUMIF(C$5:C$317,"0.5") - column C can contain any value
between 1 and 50 and I need the sum, but I only need it to use this if
cells
in column B meet a particular criteria, as per my previous question.


Thanks