COUNTIF/SUMPRODUCT unique dates+....

[RoundaboutCJP]

Hi Excel pro's!
I can't seem to put some bits of formulas together to do the whole job.

I have a list of dates in range A1:A3000.
The dates span 7 years, some date cells in the range are blank, some dates appear more than once.

I want to count:
Number of UNIQUE dates
that are Sunday
in a specified year (so the result will always be less than 52)
and don't fall between the dates specified in cell B2 and B3 (holiday week)

It needs to ignore the blank cells in the range.

Thanks so much everyone.

[sandy666]

You've 2016 so use PowerQuery (Get&Transform)

[Special-K]

Try

=SUMPRODUCT((WEEKDAY(A1:A3000)=1)*(YEAR(A1:A3000)=B1)*((A1:A3000<B2)+(A1:A3000>B3))*((A1:A3000<>"")/COUNTIF(A1:A3000,A1:A3000&"")))

[RoundaboutCJP]

Like a charm.

Legend

Thank you

[RoundaboutCJP]

Further questions:

1) What is the correct way to add a criteria to the SUMPRODUCT so that it ignores entries where cell B includes the word "No" anywhere in the text string.
With COUNTIF I would use: (B1:B3000,"<>*No*")

For example:
=SUMPRODUCT((WEEKDAY(A1:A3000)=1)*(B1:B3000,"<>*No*")*(YEAR(A1:A3000)=B1)*((A1:A3000<B2)+(A1:A3000>B3))*((A1:A3000<>"")/COUNTIF(A1:A3000,A1:A3000&"")))

2) What is the correct way to add a criteria for getting it to ignore entries where the date in A matches the date in cell G1.
(A1:A3000,"<>"&G1)

Thanks so very much

[Special-K]

1) Include the extra parameter *(ISNUMBER(SEARCH("No",A1:A5)))

2) Include the extra parameter *(A1:A3000<>G1)

[RoundaboutCJP]

Hi Special-K
Thanks

Number 2 works perfectly.
Re Number 1: *(ISNUMBER(SEARCH("No",A1:A5)))
Is this counting or excluding those cells that have "No" in the cells A1:A5 ??

I want it to exclude cells that contain the word "No" anywhere within the text string of that cell.
For example, all of the following would be excluded: 'No cars' 'cars that say no', 'little no big'

THANKS

[Special-K]

Oops! Good point, it's including, it should probably be

*(NOT(ISNUMBER(SEARCH("No",A1:A5))))

[RoundaboutCJP]

Ah ha, spot on, thank you.
Learnt loads from this thread and hugely appreciate your time