| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CountIf - 2 Criteria | Excel Discussion (Misc queries) | |||
| Two criteria for CountIf? | Excel Worksheet Functions | |||
| Countif using format criteria not number criteria? | Excel Worksheet Functions | |||
| Countif using format criteria....not number criteria? | Excel Worksheet Functions | |||
| countif criteria | Excel Worksheet Functions | |||
Linear Mode
