criteria in multiple columns

[Guest]

I am trying to get a count of rows where the date column has a specific date
and a second column is marked with an "X".
Example:
Col F is a column of dates
Col L is marked with an "X" by the end user if certain events have taken
place.

Please help.

 

[T. Valko]

Try this:

=SUMPRODUCT(--(F1:F10=DATE(2007,1,1)),--(L1:L10="X"))

Or, use cells to hold the criteria:

A1 = some date like 1/1/2007
B1 = X

=SUMPRODUCT(--(F1:F10=A1),--(L1:L10=B1))

Biff

 

[Guest]

One way:
=SUMPRODUCT(($F$2:$F$100=--"01-Jan-2007")*($L$2:$L$100="X"))

 

[Guest]

The =SUMPRODUCT works perfectly. You are the best. Thank you so much!

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

The =SUMPRODUCT works perfectly. You are the best. Thank you so much!

 

[Guest]

Can you please tell me how to make this work with a date range (ex: 3/1/07 -
3/31/07) also?

 

[T. Valko]

Try one of these:

=SUMPRODUCT(--(F1:F10>=DATE(2007,3,1)),--(F1:F10<=DATE(2007,3,31)),--(L1:L10="X"))

Or:

A1 = start date 3/1/2007
B1 = end date 3/31/2007
C1 = X

=SUMPRODUCT(--(F1:F10>=A1),--(F1:F10<=B1),--(L1:L10=C1))

Biff

 

[Guest]

Once again you are a lifesaver! Thanks

Try one of these:

=SUMPRODUCT(--(F1:F10>=DATE(2007,3,1)),--(F1:F10<=DATE(2007,3,31)),--(L1:L10="X"))

Or:

A1 = start date 3/1/2007
B1 = end date 3/31/2007
C1 = X

=SUMPRODUCT(--(F1:F10>=A1),--(F1:F10<=B1),--(L1:L10=C1))

Biff