Sumifs array formula help

[silverback24]

I found a way to get sumifs to work for my spreadsheet. My question is whether there is a better way or a faster way to do this.
My sheet has 100k+ rows, there are 6 columns this formula checks against for a certain part, once it is found it checks month or date(depending on sheet) and adds quantity sold.

Here's the formula:
=IF(SUMIFS(number,part1,$A2,WEEK,B$1)>0,SUMIFS(number,part1,$A2,WEEK,B$1),IF(SUMIFS(number,part2,$A2,WEEK,B$1)>0,SUMIFS(number,part2,$A2,WEEK,B$1),IF(SUMIFS(number,part3,$A2,WEEK,B$1)>0,SUMIFS(number,part3,$A2,WEEK,B$1),IF(SUMIFS(number,part4,$A2,WEEK,B$1)>0,SUMIFS(number,part4,$A2,WEEK,B$1),IF(SUMIFS(number,part5,$A2,WEEK,B$1)>0,SUMIFS(number,part5,$A2,WEEK,B$1),IF(SUMIFS(number,part6,$A2,WEEK,B$1)>0,SUMIFS(number,part6,$A2,WEEK,B$1),0))))))

Chart Sheet is set up like this:
[TABLE="width: 280"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]Part[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]522X[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]923X[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]923X[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8750X[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0
[/TD]
[/TR]
[TR]
[TD]8750X[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]8255X[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

Data Sheet is set up like this:
[TABLE="width: 1373"]
<colgroup><col><col span="6"><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]SKU[/TD]
[TD]PART 1[/TD]
[TD]PART 2[/TD]
[TD]PART 3[/TD]
[TD]PART 4[/TD]
[TD]PART 5[/TD]
[TD]PART 6[/TD]
[TD]SKU[/TD]
[TD]Unit Price[/TD]
[TD]Invoice Quantity[/TD]
[TD]Invoice Date[/TD]
[TD]Month[/TD]
[TD]Week Number[/TD]
[/TR]
[TR]
[TD]8643X[/TD]
[TD]8643X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8643X-Chevrolet-Avalanche2500[/TD]
[TD="align: right"]107.69[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8/27/2010[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]35[/TD]
[/TR]
[TR]
[TD]641X/618X/687AC/340C[/TD]
[TD]641X[/TD]
[TD]618X[/TD]
[TD]687AC[/TD]
[TD]340C[/TD]
[TD][/TD]
[TD][/TD]
[TD]641X/618X/687AC/340C---Fitment 2[/TD]
[TD="align: right"]169.95[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/7/2010[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]37[/TD]
[/TR]
[TR]
[TD]8359X/8362X/1058C/1057C[/TD]
[TD]8359X[/TD]
[TD]8362X[/TD]
[TD]1058C[/TD]
[TD]1057C[/TD]
[TD][/TD]
[TD][/TD]
[TD]8359X/8362X/1058C/1057C-Dodge-Magnum[/TD]
[TD="align: right"]239.95[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/14/2010[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]972X/973X/906C/828C[/TD]
[TD]972X[/TD]
[TD]973X[/TD]
[TD]906C[/TD]
[TD]828C[/TD]
[TD][/TD]
[TD][/TD]
[TD]972X/973X/906C/828C---Fitment 1[/TD]
[TD="align: right"]199.9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/16/2010[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]482X/426X/687C/340C[/TD]
[TD]482X[/TD]
[TD]426X[/TD]
[TD]687C[/TD]
[TD]340C[/TD]
[TD][/TD]
[TD][/TD]
[TD]482X/426X/687C/340C-VW-Jetta 1[/TD]
[TD="align: right"]169.95[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/17/2010[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]38[/TD]
[/TR]
[TR]
[TD]8557X/8582X/679C/711C[/TD]
[TD]8557X[/TD]
[TD]8582X[/TD]
[TD]679C[/TD]
[TD]711C[/TD]
[TD][/TD]
[TD][/TD]
[TD]8557X/8582X/679C/711C---Fitment 3[/TD]
[TD="align: right"]280.45[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]9/20/2010[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]39[/TD]
[/TR]
</tbody>[/TABLE]


Basically, I am trying to set up a tool where my office can actually analyze our listing and possible trends instead of maintenance.

These will be reference data for each year that a macro will look up and show when searched. Still in gathering data stage currently.

Thanks in advance.

 

[sijpie]

You are not saying what the various parameters in your formula are:
number
part
A2
week
B1

is the formula located in the data sheet or in the chart sheet or in another sheet?
Where are these sales figures?

 

[silverback24]

Sorry about that.
The formulas are in the chart sheet. The example formula is from cell B2. the data ranges are titled after the column titles from the data page. So Part1 = Part 1, week = week num, number = invoice quantity. B$1 is the number the sumifs is searching for in the week num column. $A2 is the part the sumifs is looking for in the 6 part columns.

I am looking into SQL and Access for this also would these be better at handling this type of data?

The above is only one part of the package I'm trying to put together.

 

[jamcall]

Could try this to see if it gives you what you're looking for...

=SUM((parts=$A2)*(week=B$1)*number)

It's an array formula, so enter with Ctrl-Shift-Enter.
A2 = the part being looked up from Chart
B1 = the week # from Chart
parts = the range of all parts - $B:$G (?)

After it's entered as array formula, you can drag it to the other cells in Chart - as you would with a regular formula.

Not sure how much faster it is than all the SUMIFS, but it looks cleaner.

HTH,
~ Jim

 

[sijpie]

My son, a software engineer, keeps telling me to stop using Excel for database purposes. So in real terms you would be better using Access or some SQL database. But it depends of course on how big the database is going to be, who is going to use it, how often... Because db setting up and programming is always a bit more work.