Sumproduct problem

**Logie3000** · 09-11-2011, 01:09 PM

Hi, first time posting on the forum, and hoping for some help.

I am using a sumproduct formula to count how many instances where 3 criteria are being met. However, it does not seem to be working. I have looked all over the place and changed different bits of the formula, but cannot solve the issue.

The formula is:

=SUMPRODUCT(('Contract tracker'!J4:J1000>='After the event measures month'!C2)*('Contract tracker'!J4:J1000<='After the event measures month'!D2)*(COUNTIF('Contract tracker'!R4:R10000,">-1")))

I have done shift, ctrl, return to make it an array formula.

For reference, C2 is a date, D2 is a date.

I am looking to find how many contracts were requested between 2 dates and out of these how many gave at least 0 days notice before the starting date.

Thanks in advance

**Logie3000** · 09-11-2011, 01:11 PM

I am using excel 2010 in case you wondered.

**TMShucks** · 09-11-2011, 01:41 PM

SUMPRODUCT is *not* an Array formula.

If you're using Excel 2010, why not use COUNTIFS or SUMIFS?

Regards

**Logie3000** · 09-11-2011, 01:57 PM

Thanks for the reply. Even when I remove array, it does not give me the correct answer.

When I tried to use countifs, I could not get the correct answer either. Don't suppose you could suggest the countifs formula and I will give it a try?

**TMShucks** · 09-11-2011, 02:14 PM

Please post a sample workbook with some typical data ... only interested in the relevant data.

Regards

**daddylonglegs** · 09-11-2011, 02:21 PM

You wouldn't normally use COUNTIF within SUMPRODUCT, try like this

=SUMPRODUCT(('Contract tracker'!J4:J1000>='After the event measures month'!C2)*('Contract tracker'!J4:J1000<='After the event measures month'!D2)*('Contract tracker'!R4:R10000>0))

or with COUNTIFS that would be

=COUNTIFS('Contract tracker'!J4:J1000,">="&'After the event measures month'!C2,'Contract tracker'!J4:J1000,"<="&'After the event measures month'!D2,'Contract tracker'!R4:R10000,">0")