Counting cells

M

Mosqui

I need to count some cells with dates with conditions, count if something is
on the next column.

26 9/12/05
30 9/12/05
26 9/12/05

So, I want to know how many dates I got on 26 (2off)and on 30(1 off).

Is this clear?, sorry if is not.

Thanks

Mosqui
 
M

Max

One guess ..

Assuming this data is in Sheet1, cols A & B, from row1 down
26 9/12/05
30 9/12/05
26 9/12/05
Click to expand...

In Sheet2
--------
Listed in A1 down are the numbers: 26, 30, ..

Put in B1:

=SUMPRODUCT((Sheet1!$B$1:$B$10<>"")*(Sheet1!$A$1:$A$10=A1))

Copy B1 down

This returns:

26 2
30 1
etc

And if you want the text "off" joined with the count result,

Put instead in B1 and copy down:
=SUMPRODUCT((Sheet1!$B$1:$B$10<>"")*(Sheet1!$A$1:$A$10=A1)) & " off"

Adapt the ranges to suit,
but note that we can't use entire col refs in SUMPRODUCT
 
Top