Formula Help

[AnnaV]

How do I enter a formula that counts based on two different criteria. I
column A I have names and in column B I have dates or blank cells.
need a formula that counts if there is a date in a specified cell i
column B.

The formula I have now is

=SUMPRODUCT(A1:A12="Scratch")*(B1:B12="?????"))

What would I put in the ????? to make it count if the cell has a date

 

[dscarter7]

The only thing I can think of would be a two step process. In C1, us
the formula = if(and(isnumber(B1),A1="Scratch"),1,0), and so on dow
the column.

Then, just sum column C

 

[Bernie Deitrick]

Anna,

Dates in Excel are just numbers, formatted as dates. The only way to tell
the formatting is to use a column of helper formulas (CELL is not array
enabled). In C1, enter

=LEFT(CELL("Format",B1),1)="D"

and copy down to C12.

Then use the formula

=SUMPRODUCT((A1:A12="Scratch")*(C1:C12))

HTH,
Bernie
MS Excel MVP