Sumproduct formula problem

**sclang** · 06-03-2008, 11:28 AM

Hello all,
I have a database that has named column ranges "terr, type, date, & time"
I want to sum all the values in the "time" range that match the terr#, Type, and between the two dates of Startdate and Enddate.

The formula below works but comes back with a very low amount so its either not summing the time and only counting instances or ????

I do not know. Help

Thanks for any help you can give....

=SUMPRODUCT(--(Terr=1),--(Type="Regular Call Cycle"),--(INT(date)>=StartDate),--(INT(date)<=EndDate),Time)

**NBVC** · 06-03-2008, 11:33 AM

The things I would question are:

1. Are you sure your named ranges cover all the database records?

2. Are the values in Terr all formatted correctly as numbers? Go to Data|Text to Columns and click Finish... should change all to numbers...

3. Are the supposed matches in Type exact matches? I.e. no extra spaces, chars, etc in any of the fields.

If still not right, attach the spreadsheet for us to review.

**sclang** · 06-03-2008, 01:35 PM

Here is the sheet that is causing me the grief....

Thanks all....

**NBVC** · 06-03-2008, 02:05 PM

The formula you have in the spreadsheet does not look like the formula you posted originally in this thread.

Here is what you have in the spreadsheet:

=SUMPRODUCT((terr=1),--(type="Regular Call Cycle"),--INT(date)>=startdate)--(INT(date)<=enddate)*(time)

You have missing --, you have missing commas, you have missing opening parentheses, etc....

The formula you posted originally, when pasted in your sheets results in 765

=SUMPRODUCT(--(Terr=1),--(Type="Regular Call Cycle"),--(INT(date)>=StartDate),--(INT(date)<=EndDate),Time)

**sclang** · 06-03-2008, 03:08 PM

Sorry, I was experimenting with the formula so the one in the sheet might have been different.

The formula works great and since you fixed up the missing elements I included the index functions and it now works the way I need it to.

=SUMPRODUCT(--(INDEX(data,,2)=B10),--(INDEX(data,,10)="Regular Call Cycle"),--(INT(INDEX(data,,5))>=startdate),--(INT(INDEX(data,,5))<=enddate),INDEX(data,,14))

How about the other formula to Count of all unique location#s, between the start and end date, and match terr#?? Is this even possible in a formula?

If it is, I need to learn much more about advanced formulas.

Thanks a million........

O