countifs - what is instead in excel 2000???

amir2000 wrote....
....
You think it's possible to tell it to count only once and not every date to
count it again?
....

Example. In B2:H7 (columns C, E and G blank, dates in yyyy-mm-dd
format),

2008-06-03 2008-06-02 2008-06-01 box 2
2008-06-02 2008-06-01 2008-06-03 box 3
2008-06-02 2008-06-01 2008-06-03 --
2008-06-02 2008-06-02 2008-06-01 box 5
2008-06-02 2008-06-02 2008-06-01 --
2008-06-03 2008-06-03 2008-06-03 box 7

The formula

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),
--(($B$2:$B$7=39601)+($D$2:$D$7=39601)+($F$2:$F$7=39 601)0))

returns 3.

Isn't that the correct result for this data? Note that B5 and D5 both
equal the date 2008-06-02, which equals 39601, but the 'box' in column
H is only counted once.

Thanks, now it's ok for the same date.
But, I need it not to read again if the next date is in the next date column.
It need to count each cell of box once regarding the date but showing me in
which date it was done.
I see it as a chalenge ;-)

Amir
--
www.amir2000.nl


"Harlan Grove" wrote:

amir2000 wrote....
....
You think it's possible to tell it to count only once and not every date to
count it again?
....

Example. In B2:H7 (columns C, E and G blank, dates in yyyy-mm-dd
format),

2008-06-03 2008-06-02 2008-06-01 box 2
2008-06-02 2008-06-01 2008-06-03 box 3
2008-06-02 2008-06-01 2008-06-03 --
2008-06-02 2008-06-02 2008-06-01 box 5
2008-06-02 2008-06-02 2008-06-01 --
2008-06-03 2008-06-03 2008-06-03 box 7

The formula

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),
--(($B$2:$B$7=39601)+($D$2:$D$7=39601)+($F$2:$F$7=39 601)0))

returns 3.

Isn't that the correct result for this data? Note that B5 and D5 both
equal the date 2008-06-02, which equals 39601, but the 'box' in column
H is only counted once.

amir2000 wrote...
....
It need to count each cell of box once regarding the date but
showing me in which date it was done.
....

Unclear.

Using your example from a few responses back, with *ALL* dates
formatted as yyyymmdd,

A1:H7
QTY 1__Date 1____QTY 2__Date 2____QTY 3__Date 3____total__Line 1
___10__20080206___________________________________ ____10__box 1
____5__20080306______5__19000105__________________ ____10__box 1
____3__20080206______2__19000102______5__20080406_ ____10__box 1
____5__20081206______5__19000105__________________ ____10__box 2
___10__20080206___________________________________ ____10__box 1
___10__20080606___________________________________ ____10__box 4


And in another range, which I'll assume would be B11:C17,

20080206__3
20080306__2
20080406__2
20080506__1
20080606__1

Total_____9


You want the formulas for C11:C17. Try

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),
--(($B$2:$B$7=B11)+($D$2:$D$7=B11)+($F$2:$F$7=B11)0 ))

in C11 and fill C11 down into C12:C15. These formulas return the
following results in C11:C15.

3
2
2
1
1