formula to count based on data in two different cells/columns

C

Cachod1

Column A has dates
Column B has one of 5 variables (LTF, WC, Exp, an "X", or is blank)

At the bottom of column B, I need to calculate the total number of cells
with "LTF, WC, or Exp" that have a date in column A that is equal to or less
than today's date.

What formula will do this?
 
D

Dave O

I mocked up data with dates in A2:A16 and LTF, WC, etc in B2:B16. Cell
A18 contains the "equal to or less than" date; A19:A23 contain the LTF,
WC, etc notations. The formula in cell B19 is
=SUMPRODUCT(--($A$18>=$A$2:$A$16),--(A19=$B$2:$B$16))

Copy and paste that into B20:B23.
 
E

Elkar

Try this:

=SUMPRODUCT(--(A1:A100<=TODAY()),--(B1:B100="WTC"))+SUMPRODUCT(--(A1:A100<=TODAY()),--(B1:B100="LTF"))+SUMPRODUCT(--(A1:A100<=TODAY()),--(B1:B100="Exp"))

There may be a more efficient way to write this, but it should get the
results you're looking for.

HTH,
Elkar
 
Top