COUNTIF with 2 criteria
 

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.

COUNTIF with 2 criteria
 

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

or

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

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



"John Moore" wrote:

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.

COUNTIF with 2 criteria
 

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

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

"John Moore" wrote:

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.

COUNTIF with 2 criteria
 

....missed last bracket ..

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

"Toppers" wrote:

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

or

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

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



"John Moore" wrote:

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.

COUNTIF with 2 criteria
 

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 .... ?

"Sloth" wrote:

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

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

"John Moore" wrote:

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.

COUNTIF with 2 criteria
 

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

"John Moore" wrote in message
...
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 .... ?

"Sloth" wrote:

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

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

"John Moore" wrote:

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.

COUNTIF with 2 criteria
 

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

"Bob Umlas" wrote:

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

"John Moore" wrote in message
...
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 .... ?

"Sloth" wrote:

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

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

"John Moore" wrote:

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.