| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif Criteria (2 diff columns)
Column B is a list of dates in no particular order (10-01-07, 10-03-07,
etc.), column D lists different texts (TRAIN & CAR). How do I count the number of dates according to criteria I specify (10-1-07),(TRAIN) - returning the number of times 10-1-07 occured for TRAINS? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif Criteria (2 diff columns)
Use SUMPRODUCT if you have more than one criteria to check. For this
example, I'll assume your criteria are in cells A1 and A2. =SUMPRODUCT(--(B1:B100=A1),--(D1:D100=A2)) Since SUMPRODUCT only works with numbers, and the statement B1:B100=A1 returns either TRUE or FALSE, the -- is used to convert TRUE/FALSE into 1 or 0. Thus, only when both conditions are TRUE (or 1) will they be counted. HTH, Elkar "Teddy-B" wrote: Column B is a list of dates in no particular order (10-01-07, 10-03-07, etc.), column D lists different texts (TRAIN & CAR). How do I count the number of dates according to criteria I specify (10-1-07),(TRAIN) - returning the number of times 10-1-07 occured for TRAINS? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif Criteria (2 diff columns)
Excellent -
Thank you for the right answer. This was very helpful. ~Teddy-B "Elkar" wrote: Use SUMPRODUCT if you have more than one criteria to check. For this example, I'll assume your criteria are in cells A1 and A2. =SUMPRODUCT(--(B1:B100=A1),--(D1:D100=A2)) Since SUMPRODUCT only works with numbers, and the statement B1:B100=A1 returns either TRUE or FALSE, the -- is used to convert TRUE/FALSE into 1 or 0. Thus, only when both conditions are TRUE (or 1) will they be counted. HTH, Elkar "Teddy-B" wrote: Column B is a list of dates in no particular order (10-01-07, 10-03-07, etc.), column D lists different texts (TRAIN & CAR). How do I count the number of dates according to criteria I specify (10-1-07),(TRAIN) - returning the number of times 10-1-07 occured for TRAINS? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif Criteria (2 diff columns)
Can you show me how to make this formula solid using INDIRECT?
"Elkar" wrote: Use SUMPRODUCT if you have more than one criteria to check. For this example, I'll assume your criteria are in cells A1 and A2. =SUMPRODUCT(--(B1:B100=A1),--(D1:D100=A2)) Since SUMPRODUCT only works with numbers, and the statement B1:B100=A1 returns either TRUE or FALSE, the -- is used to convert TRUE/FALSE into 1 or 0. Thus, only when both conditions are TRUE (or 1) will they be counted. HTH, Elkar "Teddy-B" wrote: Column B is a list of dates in no particular order (10-01-07, 10-03-07, etc.), column D lists different texts (TRAIN & CAR). How do I count the number of dates according to criteria I specify (10-1-07),(TRAIN) - returning the number of times 10-1-07 occured for TRAINS? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Countif Criteria (2 diff columns)
I'm not sure what you mean by "solid". Do you just want the cell references
to remain the same when copied? Then just use absolute references: =SUMPRODUCT(--($B$1:$B$100=$A$1),--($D$1:$D$100=$A$2)) HTH, Elkar "Teddy-B" wrote: Can you show me how to make this formula solid using INDIRECT? "Elkar" wrote: Use SUMPRODUCT if you have more than one criteria to check. For this example, I'll assume your criteria are in cells A1 and A2. =SUMPRODUCT(--(B1:B100=A1),--(D1:D100=A2)) Since SUMPRODUCT only works with numbers, and the statement B1:B100=A1 returns either TRUE or FALSE, the -- is used to convert TRUE/FALSE into 1 or 0. Thus, only when both conditions are TRUE (or 1) will they be counted. HTH, Elkar "Teddy-B" wrote: Column B is a list of dates in no particular order (10-01-07, 10-03-07, etc.), column D lists different texts (TRAIN & CAR). How do I count the number of dates according to criteria I specify (10-1-07),(TRAIN) - returning the number of times 10-1-07 occured for TRAINS? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNTIF with criteria in 2 columns | Excel Discussion (Misc queries) | |||
| Countif using criteria in multiple columns | Excel Worksheet Functions | |||
| Countif - Two Criteria in two columns are met. | Excel Discussion (Misc queries) | |||
| countif = < AND value in adjacent columns match criteria | Excel Worksheet Functions | |||
| can countif function look at 2 separate columns for criteria | Excel Worksheet Functions | |||
Linear Mode
