Im trying to perform the below, however the formula is returning nil values.

=SUMPRODUCT(--('Sheet2'!C2:C1000=B8),--('Sheet2'!G2:G1000>B4),--('Sheet2'!G2:G1000<D4))

To explain what this is attempting to do,

B8 contains a colour, for example the word red.

B4 & D4 contain dates.

The formula should basically count the number of times the word red appears in column C, sheet 2, but only when the date in column G, sheet2 is greater than or equal to B4 & less than or equal to D4

Is it possible to do this, and if so what amendments am i required to make?