date formula for extracting unique dates

  • Re: date formula for extracting unique dates


    Ooops! I forgot that you wanted the dates in ascending order. Let me work on it and I'll get back to you shortly...

  • Re: date formula for extracting unique dates


    ok, that works, now I just need to figure out how to replace the other array formulas I have in this workbook

  • Re: date formula for extracting unique dates


    OK, moving on, I need to take those dates and use them to calculate a sum from another table so at the beginning of every pool I have the sum for that date in the first column of the pool, then I need that sum subtracted from with another formula I already have in another column all the way down until it is zero, then when the next pool starts I need it to again take the sum for that pool


    I don't see how to get around this without an array formula, I already have the entire sheet filled up with columns and cannot add two criteria columns for each date for the dsum to work, any thoughts?

  • Re: date formula for extracting unique dates


    here is what I want, but I need a formula for cell I2 that I can copy to all of the yellow cells that will do what is demonstrated


    I want the first cell of a pool to have the sum from the other sheet (Sheet2 in this example) and then every following total line will subtract the total that I generated in the green cells, once that sum is exhausted it should remain zero until the next pool that also contains that date, and then it does the dsum again


    can anybody help me with this?

  • Re: date formula for extracting unique dates


    Try the following...


    I2, copied across:


    =IF($B2=I$1,SUMPRODUCT(--(Sheet2!$A$2:$A$37=$A2),--(Sheet2!$B$2:$B$37=$B2),Sheet2!$C$2:$C$37),0)


    I3, copied down and across:


    =IF(N($B3),IF($B3=I$1,IF(SUMPRODUCT(--($A$2:$A3=$A3),--($B$2:$B3=$B3))=1,SUMPRODUCT(--(Sheet2!$A$2:$A$37=$A3),--(Sheet2!$B$2:$B$37=$B3),Sheet2!$C$2:$C$37),I2),I2),I2-C3)


    Hope this helps!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!