Sumifs with too many criterias

[marcothexer]

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/1/16[/TD]
[TD="align: center"](WEEK 1)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/8/16[/TD]
[TD="align: center"](WEEK 2)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/15/16[/TD]
[TD="align: center"](WEEK 3)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/22/16[/TD]
[TD="align: center"](WEEK 4)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]PRODUCT[/TD]
[TD="align: center"]AREA[/TD]
[TD="align: center"]SALES[/TD]
[TD="align: center"]PRODUCT[/TD]
[TD="align: center"]AREA[/TD]
[TD="align: center"]SALES[/TD]
[TD="align: center"]PRODUCT[/TD]
[TD="align: center"]AREA[/TD]
[TD="align: center"]SALES[/TD]
[TD="align: center"]PRODUCT[/TD]
[TD="align: center"]AREA
[/TD]
[TD="align: center"]SALES[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]SALES PERSON 1[/TD]
[TD="align: center"]P1[/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]NJ[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]PA[/TD]
[TD="align: center"]1100[/TD]
[TD="align: center"]P2[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]800[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]SALES PERSON 2[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]PA[/TD]
[TD="align: center"]800[/TD]
[TD="align: center"]P1[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]P4[/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]1900[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]NJ[/TD]
[TD="align: center"]1200[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]SALES PERSON 3[/TD]
[TD="align: center"]P2[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]1000[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]1600[/TD]
[TD="align: center"]P1[/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]800[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]SALES PERSON 4[/TD]
[TD="align: center"]P4[/TD]
[TD="align: center"]NJ[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]P2[/TD]
[TD="align: center"]PA[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]P4[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]900[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]SALES PERSON 5[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]1200[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]NY[/TD]
[TD="align: center"]1600[/TD]
[TD="align: center"]P2[/TD]
[TD="align: center"]CT[/TD]
[TD="align: center"]600[/TD]
[TD="align: center"]P3[/TD]
[TD="align: center"]NJ[/TD]
[TD="align: center"]700[/TD]
[/TR]
</tbody>[/TABLE]


Sales persons on different states with different Products and their sales figures.
Every 3 column is dedicated to a week.
The actual table extends up to 12 weeks and around 60 sales persons and P3 is my focused product.
How can I;
Sum up the sale totals of each sales person, (lets say) from week 2 until week 6?
Sum up the sale totals of Product 3, (lets say) from week 2 until week 6 for each sales person?

Thanks.

 

[bosco_yip]

1] Let your data of sale table from Week 1 to Week12 housed at A1:AK7

2] Summary cell AM1 heading : All total sale from week 2 to week 6

3] In AM3, formula copy down :

=SUM(INDEX(B3:AK3,0,MATCH("(WEEK 2)",B$1:AK$1,0)+1):INDEX(B3:AK3,0,MATCH("(WEEK 6)",B$1:AK$1,0)+1))

4] Summary cell AN1 heading : Total sale of Product 3 from week 2 to week 6

5] In AN3, formula copy down :

=SUMIF(INDEX(B3:AK3,0,MATCH("(WEEK 2)",B$1:AK$1,0)-1):INDEX(B3:AK3,0,MATCH("(WEEK 6)",B$1:AK$1,0)-1),"P3",INDEX(B3:AK3,0,MATCH("(WEEK 2)",B$1:AK$1,0)+1))

Regards

 

[marcothexer]

Hi
Thanks for the reply. But Im getting #N/A. I wonder if I do something wrong?!?!

 

[FDibbins]

They both worked for me - after I changed the week 6 to week 4 in your sample data, and adjusting the range
=SUM(INDEX(B3:M3,0,MATCH("(WEEK 2)",B$1:M$1,0)+1):INDEX(B3:M3,0,MATCH("(WEEK 4)",B$1:M$1,0)+1))
=SUMIF(INDEX(B3:AM3,0,MATCH("(WEEK 2)",B$1:M$1,0)-1):INDEX(B3:M3,0,MATCH("(WEEK 4)",B$1:MK$1,0)-1),"P3",INDEX(B3:M3,0,MATCH("(WEEK 2)",B$1:M$1,0)+1))

 

[marcothexer]

Trying to figure out what do those -1 and +1 do in MATCH?

Would it be easier if I worked with the dates where they are instead of the text WEEK 2 WEEK 3 etc?

1] Let your data of sale table from Week 1 to Week12 housed at A1:AK7

2] Summary cell AM1 heading : All total sale from week 2 to week 6

3] In AM3, formula copy down :

=SUM(INDEX(B3:AK3,0,MATCH("(WEEK 2)",B$1:AK$1,0)+1):INDEX(B3:AK3,0,MATCH("(WEEK 6)",B$1:AK$1,0)+1))

4] Summary cell AN1 heading : Total sale of Product 3 from week 2 to week 6

5] In AN3, formula copy down :

=SUMIF(INDEX(B3:AK3,0,MATCH("(WEEK 2)",B$1:AK$1,0)-1):INDEX(B3:AK3,0,MATCH("(WEEK 6)",B$1:AK$1,0)-1),"P3",INDEX(B3:AK3,0,MATCH("(WEEK 2)",B$1:AK$1,0)+1))

Regards

 

[marcothexer]

Would you adjust that to work for the dates instead of the text WEEK 1 WEEK 2 etc?

They both worked for me - after I changed the week 6 to week 4 in your sample data, and adjusting the range
=SUM(INDEX(B3:M3,0,MATCH("(WEEK 2)",B$1:M$1,0)+1):INDEX(B3:M3,0,MATCH("(WEEK 4)",B$1:M$1,0)+1))
=SUMIF(INDEX(B3:AM3,0,MATCH("(WEEK 2)",B$1:M$1,0)-1):INDEX(B3:M3,0,MATCH("(WEEK 4)",B$1:MK$1,0)-1),"P3",INDEX(B3:M3,0,MATCH("(WEEK 2)",B$1:M$1,0)+1))

 

[FDibbins]

Trying to figure out what do those -1 and +1 do in MATCH?

the MATCH is finding an exact match (say 3...ie column 3), the +1 increases that to 4. Likewise the -1 reduces that back to column 2

Using real dates is almost always better that some form of "text date"

To modify that to real dates - and I suggest you put the dates in their own cell, to give more flexibility, say X1 and Z1...
=SUM(INDEX(B3:M3,0,MATCH($X$1,B$1:M$1,0)+1):INDEX(B3:M3,0,MATCH($Z$1,B$1:M$1,0)+1))

 

[marcothexer]

Thanks,

Your formula is simple to understand now but i cant make it work. I will keep trying.

the MATCH is finding an exact match (say 3...ie column 3), the +1 increases that to 4. Likewise the -1 reduces that back to column 2

Using real dates is almost always better that some form of "text date"

To modify that to real dates - and I suggest you put the dates in their own cell, to give more flexibility, say X1 and Z1...
=SUM(INDEX(B3:M3,0,MATCH($X$1,B$1:M$1,0)+1):INDEX(B3:M3,0,MATCH($Z$1,B$1:M$1,0)+1))