If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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. |
#22
|
|||
|
|||
countifs - what is instead in excel 2000???
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. |
#23
|
|||
|
|||
countifs - what is instead in excel 2000???
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 |
Thread Tools | |
Display Modes | |
|
|