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

[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?

[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.

[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

"Cachod1" wrote:

> 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?

[pinmaster]

Try this:

=SUMPRODUCT((A1:A5<>"")*(A1:A5<=TODAY())*(B1:B5={"wc","exp","ltf"}))

"Cachod1" wrote:

> 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?