Problem With Sumproduct

[bopsgtir]

Hi all im trying to get the below formula to work, but its not quite right, What im trying to ask it is to look in column D and check the Date against cell B9, then check column R to make sure it says One Man Crew, also make sure column B doesnt say DDR then add the totals in column AQ, but the below column doesnt seem to take into account the first part, checking against the date (--(North!$D$2:$D$50000=B9)

Has anyone got any ideas what ive done wrong.

SUMPRODUCT(--(North!$D$2:$D$50000=B9)--(North!$R$2:$R$50000="One Man Crew"),--(+NOT(North!$B$2:$B$50000="DDR")),--(North!$AQ$2:$AQ$50000))

[zbor]

You missing a delimiter:

SUMPRODUCT(--(North!$D$2:$D$50000=B9),--(North!$R$2:$R$50000="One Man Crew"),--(NOT(North!$B$2:$B$50000="DDR")),--(North!$AQ$2:$AQ$50000))

Also, I wouls suggest you to keep range lower (if you don't have 50.000 rows to use... to 5.000, or how much you need)

[bopsgtir]

perfect thank you for that, the sheet is around 30,000 lines. what is the reasoning of lowering the number?

[zbor]

SUMPRODUCT is a slow function that take lot of resources.
If you use 2007, as your profile say, then you should use SUMIFS function that is faster.