Sumproduct Offset alternative needed

**samshiells** · 08-06-2012, 10:54 AM

Hi All,

I currently have a spreadsheet which sums a Complaint_Count based on three conditions. (Date, Complaint_Description & DailyFilter) This data is kept on a RawData tab where it is sorted by Date.

I'm able to sum them using SumProduct and that works fine, however it was taking a while to sum due to the size of the data. (I'd used named ranges, etc) So what I then did was to use a match query to locate the data within the RawData tab to produce a range where it is (e.g 1/7/2012 starts on row 7900 and finishes on 8000) This coupled with an Offset query sped up my worksheet but have since found out it is a volitile function.

I would like to be able to do exactly the same thing but without using the Offset. I have heard that I may be able to use Index instead? But am not sure.

My current formula I have is as follows:

Cell Q10: =MATCH(Q12,RawData!$G:$G,0) - This finds the first row
Cell Q11: =MATCH(Q12,RawData!$G:$G) - This finds the last row
Cell Q14: =SUMPRODUCT(--(OFFSET(RawData!$E$1,Q$10-1,0,Q$11-Q$10+1,1)=$C14),--(OFFSET(RawData!$P$1,Q$10-1,0,Q$11-Q$10+1,1)=$AT$5),--(OFFSET(RawData!$O$1,Q$10-1,0,Q$11-Q$10+1,1)))

Cell Q12: This has the date I want to filter by.
Cell C14: This is the Complaint_Description which is in column E:E of RawData
Cell AT5: This is the DailyFilter which is in column P:P of RawData
Column O:O of RawData has Complaint_Count which is the field I'm summing.

Unfortunately I can't use any array formulas as I have merged cells(!) and I can't change the layout. If there is any vital information I have missed please let me know and I will try my best to answer!

Thanks, Sam

(sorry for the essay!)

**Richard Buttrey** · 08-06-2012, 11:14 AM

Hi,

If you are using Excel 2007 then you won't need array formulae like SUMPRODUCT(). You can use =COUNTIFS() - note the plural. This will be much faster than an array formula when you have lots of data rows.

It shouldn't matter that you have merged cells - although for future reference avoid them like the plague, they are just too much trouble. (If you're only using them for formatting and centering text then use the format 'Center Across Selection' option.

If you need further help upload a workbook and manually add some example results.

Regards

**samshiells** · 08-06-2012, 11:33 AM

Hi,

I would love to use SUMIFS but it has to be compatible with 2003 unfortunately! I have (hopefully!) attached a smaller version of the spreadsheet that I'm putting together.

Any other suggestions would be greatly received!

Thanks, Sam

**daddylonglegs** · 08-06-2012, 11:38 AM

Rather than OFFSET like this

OFFSET(RawData!$E$1,Q$10-1,0,Q$11-Q$10+1,1)

you can use INDEX like this....

=INDEX(RawData!$E$E,Q$10):INDEX(RawData!$E$E,Q$11)

**samshiells** · 08-06-2012, 11:58 AM

Amazing - thank you so much! I've been trying to get my head round this for so long! Should've come here a long time ago!

Thanks, Sam

**Cutter** · 08-06-2012, 12:18 PM

If you are satisfied with the solution(s) provided, please remember to mark your thread as Solved.

New quick method:
Select Thread Tools (above first post on page) -> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix".
Change to "Solved"
Click Save