countifs - what is instead in excel 2000???

[Harlan Grove]

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=39601)>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]

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

 

[Harlan Grove]

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