Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
count of column b when criteria = conditions of both columns a and
EXCEL 2003 - I need a formula to "Count" the number of products listed in
column B, C or D for a particular state listed in column a when input the state in a specific cell. The difference between the numbers in product A vs product B vs product C columns is the order of preference of "A" over "B" over "C". I've tried the CountA and DcountA but I'm missing something. Something along the lines of: Count the number of times product A is listed for state of TX. Then Product B and so on. I actually have 5 product columns and several thousand entries for the state since ech state has several cities. input "TX" Product A Product B Product C a b c d State Product A Product B Product C CA 1 2 TX 1 2 CA 1 2 CA 1 2 3 TX 1 2 TX 1 2 TX 1 2 3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
count of column b when criteria = conditions of both columns a and
One way:
=SUMPRODUCT(--(A2:A10000="TX"),--(B1:B10000<"")) See http://www.mcgimpsey.com/excel/doubleneg.html for an explanation. OTOH, your situation seems to be right up the Pivot Table alley... http://peltiertech.com/Excel/Pivots/pivotstart.htm In article , Roy Ringrose wrote: EXCEL 2003 - I need a formula to "Count" the number of products listed in column B, C or D for a particular state listed in column a when input the state in a specific cell. The difference between the numbers in product A vs product B vs product C columns is the order of preference of "A" over "B" over "C". I've tried the CountA and DcountA but I'm missing something. Something along the lines of: Count the number of times product A is listed for state of TX. Then Product B and so on. I actually have 5 product columns and several thousand entries for the state since ech state has several cities. input "TX" Product A Product B Product C a b c d State Product A Product B Product C CA 1 2 TX 1 2 CA 1 2 CA 1 2 3 TX 1 2 TX 1 2 TX 1 2 3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count on conditions for two Columns | Excel Worksheet Functions | |||
COUNT based on conditions in 2 or more columns | Excel Worksheet Functions | |||
how to count 2 date columns conditions. | Excel Discussion (Misc queries) | |||
Count if on Single Criteria with multiply conditions | Excel Worksheet Functions | |||
count all the columns in a range with certain conditions | Excel Discussion (Misc queries) |