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?