Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum items in a column based on criteria located in other columns | Excel Discussion (Misc queries) | |||
countif = < AND value in adjacent columns match criteria | Excel Worksheet Functions | |||
Excel COUNTIF Function - a range as criteria ? | Excel Worksheet Functions | |||
countif criteria only one word of few doesn't work | Excel Worksheet Functions | |||
want to count in two columns; countif (colA=x AND colB=y)? | Excel Worksheet Functions |