Issues with Sumproduct

**Chareth Cutestory** · 09-02-2011, 05:08 PM

Here is the formula that I am using:

=SUMPRODUCT(('GA Data'!$CK$27:$CK$44>=$A$4)*('GA Data'!$CK$27:$CK$44<=$A$7),'GA Data'!CM2:CM19)

where: CK is Date, A4 is Start Date, A7 is End Date, CM is data to be summed.

Currently this formula is returning a 0 (when it should be ~700). When I remove the upper limitation portion of the formula *('GA Data'!$CK$27:$CK$44<=$A$7) then the correct value is returned.

The problem is that I need to include the Upper Limits so I can create a monthly report. Any ideas?

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

You have dates from row 27 to 44 but you are summing rows 2 to 19, is that right? Normally I'd expect those ranges to cover the same rows

**Chareth Cutestory** · 09-02-2011, 05:31 PM

=SUMPRODUCT(('GA Data'!$CK$2:$CK$19>=$A$4)*('GA Data'!$CK$2:$CK$19<=$A$7),'GA Data'!CM2:CM19)

Sorry about that, use this one

**daddylonglegs** · 09-02-2011, 05:48 PM

That formula looks OK syntactically, what dates do you have in A4 and A7?

**Chareth Cutestory** · 09-02-2011, 05:53 PM

A4= 08/01/2011 This is start date for report
A7= 08/23/2011 This is the end date for the report

Here is how the data is populated:

I am using MS Query to pull data from our SQL database into its own workbook, then using a separate workbook as a report "dashboard". The above formulas are located in the dashboard and are referencing the sql workbook.

**daddylonglegs** · 09-02-2011, 06:01 PM

From what you said about the formula working when you left out the upper limit......it may be that your dates in column CK are text-formatted, what do you get with this test formula on the date column?

=COUNT('GA Data'!$CK$2:$CK$19)

COUNT counts numbers (not text) and dates are just formatted numbers in Excel - if that gives zero then you have text formatted dates, you could try co-ercing them to real dates in the formula, e.g.

=SUMPRODUCT(('GA Data'!$CK$2:$CK$19+0>=$A$4)*('GA Data'!$CK$2:$CK$19+0<=$A$7),'GA Data'!CM2:CM19)

Does that work?

**Chareth Cutestory** · 09-02-2011, 06:08 PM

the data was coming in as text format and the start/end date was in mm/dd/yyy. By switching the start/end date to text format the formula works and everything is happy.