Possible to simplify this formula?

[loopa1]

Is it possible to simplify this formula so it's not as 'big'? I'd like to combine this formula with another similar one but the text length is too large writing it the way I am.

=SUMPRODUCT(ISNUMBER('DTPs'!$A$2:$A$10000)*('DTPs'!$D$2:$D$10000>=DATE(YEAR($H$8),MONTH($H$8)-6,1))*('DTPs'!$D$2:$D$10000<=DATE(YEAR($H$8),MONTH($H$8)-5,0))*('DTPs'!$N$2:$N$10000="")*('DTPs'!$I$2:$I$10000="Review")*('DTPs'!$H$2:$H$10000<='DTPs'!$F$2:$F$10000))
+
SUMPRODUCT(ISNUMBER('DTPs'!$A$2:$A$10000)*('DTPs'!$D$2:$D$10000>=DATE(YEAR($H$8),MONTH($H$8)-6,1))*('DTPs'!$D$2:$D$10000<=DATE(YEAR($H$8),MONTH($H$8)-5,0))*('DTPs'!$N$2:$N$10000="")*('DTPs'!$I$2:$I$10000<>"Review")*('DTPs'!$L$2:$L$10000="Review")*('DTPs'!$K$2:$K$10000<='DTPs'!$F$2:$F$10000))

The part I've put in bold is exactly the same in both halves. I've tried adding an 'OR' statement, but this doesn't return the correct result.
Thanks.

 

[CharlesChuckieCharles]

You could created named ranges for

'DTPs'!$A$2:$A$10000
'DTPs'!$D$2:$D$10000 etc

 

[pgc01]

Hi

Try:

=SUMPRODUCT(ISNUMBER(DTPs!$A$2:$A$10000)*(DTPs!$D$2:$D$10000>=DATE(YEAR($H$8),MONTH($H$8)-6,1))*(DTPs!$D$2:$D$10000<=DATE(YEAR($H$8),MONTH($H$8)-5,0))*(DTPs!$N$2:$N$10000="")*((DTPs!$I$2:$I$10000="Review")*(DTPs!$H$2:$H$10000<=DTPs!$F$2:$F$10000)+(DTPs!$I$2:$I$10000<>"Review")*(DTPs!$L$2:$L$10000="Review")*(DTPs!$K$2:$K$10000<=DTPs!$F$2:$F$10000)))

 

[T. Valko]

Is it possible to simplify this formula so it's not as 'big'? I'd like to combine this formula with another similar one but the text length is too large writing it the way I am.

=SUMPRODUCT(ISNUMBER('DTPs'!$A$2:$A$10000)*('DTPs'!$D$2:$D$10000>=DATE(YEAR($H$8),MONTH($H$8)-6,1))*('DTPs'!$D$2:$D$10000<=DATE(YEAR($H$8),MONTH($H$8)-5,0))*('DTPs'!$N$2:$N$10000="")*('DTPs'!$I$2:$I$10000="Review")*('DTPs'!$H$2:$H$10000<='DTPs'!$F$2:$F$10000))
+
SUMPRODUCT(ISNUMBER('DTPs'!$A$2:$A$10000)*('DTPs'!$D$2:$D$10000>=DATE(YEAR($H$8),MONTH($H$8)-6,1))*('DTPs'!$D$2:$D$10000<=DATE(YEAR($H$8),MONTH($H$8)-5,0))*('DTPs'!$N$2:$N$10000="")*('DTPs'!$I$2:$I$10000<>"Review")*('DTPs'!$L$2:$L$10000="Review")*('DTPs'!$K$2:$K$10000<='DTPs'!$F$2:$F$10000))

The part I've put in bold is exactly the same in both halves. I've tried adding an 'OR' statement, but this doesn't return the correct result.
Thanks.

The quick and easy thing to do is to take the DATE functions out of the formula and put them in cells.

A1: =DATE(YEAR($H$8),MONTH($H$8)-6,1)
B1: =DATE(YEAR($H$8),MONTH($H$8)-5,0)

=SUMPRODUCT(ISNUMBER('DTPs'!$A$2:$A$10000)*('DTPs'!$D$2:$D$10000>=A1)*('DTPs'!$D$2:$D$10000<=B1)*('DTPs'!$N$2:$N$10000="")*('DTPs'!$I$2:$I$10000="Review")*('DTPs'!$H$2:$H$10000<='DTPs'!$F$2:$F$10000))
+
SUMPRODUCT(ISNUMBER('DTPs'!$A$2:$A$10000)*('DTPs'!$D$2:$D$10000>=A1)*('DTPs'!$D$2:$D$10000<=B1)*('DTPs'!$N$2:$N$10000="")*('DTPs'!$I$2:$I$10000<>"Review")*('DTPs'!$L$2:$L$10000="Review")*('DTPs'!$K$2:$K$10000<='DTPs'!$F$2:$F$10000))

 

[loopa1]

Thanks both - will use a combination of both ideas to reduce the formula size further.

 

[loopa1]

Biff - agree. I may incorporate this too.