COUNT type function

[Chris]

Hi,

Can someone please help/advise.

I am trying to create a formula that will check a range of cells and
IF MORE THAN ONE CONDITION IS TRUE, then return a COUNT result.

For example, the range would include columns A to C and contain 5
rows. A random number of the cells in Column A contain dates and a
random number of the cells in Column C contain numbers. The
remainding cells in both columns would be blank.

I would like a formula that calculates how many cells that have dates
in Column A that meet a stated criteria (e.g. Date = Jan 1, 2005) ALSO
have numbers in the SAME ROW in Column C.

A B C
1 Jan 1, 2005 20
2 100
3 Jan 1, 2005
4 Jan 1, 2005 50
5

In this example, the result would be 2 (the value in any Column A cell
must = Jan 1, 2005 and must have a value in any Column C cell on the
same row).

Any advise would be gratefully appreciated.

Many thanks

 

[Max]

One way ..

Try: =SUMPRODUCT((A1:A10=DATE(2005,1,1))*(ISNUMBER(C1:C10)))

Adapt ranges to suit

 

[Alex Delamain]

If E1 contains the date you want to search for

=SUMPRODUCT((A1:A14=E1)*(C1:C14>0)