Sum unique dates in a given range

Hey,

I've been searching the web for way to long try to find an answer to my problem and just can't seem to get a formula to work. It seems to me that it should be straight forward but I just can't it to work. What I'm looking for is a formula that will only count unique days from a given date range that can be changed. My spreadsheet on sheet 3 will have a ton of date stamps from when work was done and will have the same day multiple times. And in sheet 2 I will have a date range present and want to get the sum of the number of days a stamp is present between the given date range. Basically just a count of days in column b on sheet 3 between the range in sheet 2 but only unique days not the same day over and over. In my attached file I would like the results to just return 2 (7/22 and 7/25 are the only stamps between the date selected) but instead I keep getting 5 (the number of times those days are present not the unique times).

Hope this makes sense and look forward to any help. I've attached a copy of how the spreadsheet would look. Thanks!

Originally Posted by oeldere

I used helpcolumns to get the result.

See the attached file.

2 solutions are added:

with pivot table.

with sumproduct (see the green cell).

Thanks I'm gonna give this a try!

Originally Posted by daffodil11

I used this formula in I2 and it did the trick:

=SUM(IF(FREQUENCY(IF(Sheet3!B2:B12>=D2,IF(Sheet3!B2:B12<=G2,Sheet3!B2:B12)),IF(Sheet3!B2:B12>=D2,IF(Sheet3!B2:B12<=G2,Sheet3!B2:B12))),1))

This formula is an array, entered with Control+Shift+Enter

Attachment 252996

Awesome, thank you too. We'll see if I can make either of these work!

OK, I got this to work... sorta! My dates in column B are there from formulas and not a hand typed date. The formulas to count them aren't recognizing them. If I hand type in the date it works but with my auto import of the date. How do I fix that? Thanks again!

Thanks, that worked!!! Think I got this figured out... well think you guys got it figured out and I copy and pasted it!

Thank you so much, great information!!!!!

-------------------------------
http://onlinepersonaltrainer.es