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
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
Okay, how's this?
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!
Don’t have an account yet? Register yourself now and be a part of our community!