Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting cells
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 |
#2
|
|||
|
|||
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 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 -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Mosqui" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting the number of cells meeting conditional formating criteria | Excel Worksheet Functions | |||
Counting unique text/number cells from a range | Excel Worksheet Functions | |||
Counting empty cells within a range of cells | New Users to Excel | |||
complex fomula: counting cells that are blank | Excel Worksheet Functions | |||
Counting blank cells in Pivot Table | Excel Worksheet Functions |