COUNTIF with 2 criteria

[John Moore]

Hi guys, need a solution to a COUNTIF problem ,,, I want to be able to count
the number the number of times a number appears in a column based on the
criter of SHIP .... e.g.

A B
1 123456 SHIP
2 123456 NOT
3 123456 SHIP
4 343536 SHIP

so from the above table the result would be 2 for 1234546 SHIP.

 

[Toppers]

=SUMPRODUCT(--(A2:A5)=123456),--(b2:b5="SHIP")

or

C1=123456 (set to number/text)
C2=SHIP

=SUMPRODUCT(--(A2:A5)=C1),--(B2:B5=C2)

 

[Sloth]

=SUMPRODUCT(--(A1:A4=123456),--(B1:B4="SHIP"))

Do a search for "SUMPRODUCT" to find other examples and explanations of why
this works.

 

[Toppers]

....missed last bracket ..

=SUMPRODUCT(--(A2:A5)=123456),--(b2:b5="SHIP"))

 

[John Moore]

How would I resolve the problem if there were numerous numbers in column A
,,, say 1000 lines and I wanted to count how many of those were showing as
SHIP ,,, so the result for the below table would be 3 .... ?

 

[Bob Umlas]

=COUNTIF(B1:B1000,"SHIP")
Bob Umlas
Excel MVP

How would I resolve the problem if there were numerous numbers in column A
,,, say 1000 lines and I wanted to count how many of those were showing as
SHIP ,,, so the result for the below table would be 3 .... ?

 

[John Moore]

not quite helping ,,, let me see if I can explain a bit more ,,,,,I want to
count the number of orders that show as SHIP but I only want to count them
once if the order number in column A appears more than once...... so I want
to be able to count the number of SHIPS in column B but only count them once
if the order number in column A appears more than once ,,,,, so the below
would return an answer of 2

A B
1 123456 SHIP
2 123456 SHIP
3 145799 NOT
4 145799 SHIP