Countif Criteria (2 diff columns)

[Guest]

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?

 

[Guest]

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),--(D1100=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

 

[Guest]

Excellent -
Thank you for the right answer. This was very helpful.

~Teddy-B

 

[Guest]

Can you show me how to make this formula solid using INDIRECT?

 

[Guest]

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