Need To SumIf A Certain Criteria And WeekNum and/or Month

[andyfox1979]

I'd like to be able to sumif based on a certain criteria (vendor code in my case, here being ACM) plus the week number. In a seperate cell i'd like to sumif by this same criteria plus the entire month. See below:


[TABLE="width: 772"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]Date[/TD]
[TD]WeekNum[/TD]
[TD]Invoice #[/TD]
[TD]PO #[/TD]
[TD]Amt[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD]1/5/2012[/TD]
[TD]1[/TD]
[TD]3042788[/TD]
[TD][/TD]
[TD]$146.41[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD]1/5/2012[/TD]
[TD]1[/TD]
[TD]3042789[/TD]
[TD][/TD]
[TD]$64.29[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD]1/5/2012[/TD]
[TD]1[/TD]
[TD]3042790[/TD]
[TD][/TD]
[TD]$159.34[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD]1/5/2012[/TD]
[TD]1[/TD]
[TD]3042791[/TD]
[TD][/TD]
[TD]$80.67[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD]1/5/2012[/TD]
[TD]1[/TD]
[TD]3042792[/TD]
[TD][/TD]
[TD]$141.95[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD]1/6/2012[/TD]
[TD]1[/TD]
[TD]3043512[/TD]
[TD][/TD]
[TD]$17.36[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD]1/13/2012[/TD]
[TD]2[/TD]
[TD]3047698[/TD]
[TD][/TD]
[TD]$52.31[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD]1/13/2012[/TD]
[TD]2[/TD]
[TD]3047699[/TD]
[TD][/TD]
[TD]$38.55[/TD]
[/TR]
</tbody>[/TABLE]


This is what my output page looks like:

[TABLE="width: 444"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Vendor[/TD]
[TD]Total[/TD]
[TD] December[/TD]
[TD] Week1 [/TD]
[TD]Week2[/TD]
[/TR]
[TR]
[TD]ACM[/TD]
[TD="align: right"]$13,284.00[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance! I'm a bit frustrato.

 

[T. Valko]

I'd like to be able to sumif based on a certain criteria (vendor code in my case, here being ACM) plus the week number. In a seperate cell i'd like to sumif by this same criteria plus the entire month. See below:


[TABLE="width: 772"]
<TBODY>[TR]
[TD]Vendor
[/TD]
[TD]Date
[/TD]
[TD]WeekNum
[/TD]
[TD]Invoice #
[/TD]
[TD]PO #
[/TD]
[TD]Amt
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042788
[/TD]
[TD][/TD]
[TD]$146.41
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042789
[/TD]
[TD][/TD]
[TD]$64.29
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042790
[/TD]
[TD][/TD]
[TD]$159.34
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042791
[/TD]
[TD][/TD]
[TD]$80.67
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042792
[/TD]
[TD][/TD]
[TD]$141.95
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/6/2012
[/TD]
[TD]1
[/TD]
[TD]3043512
[/TD]
[TD][/TD]
[TD]$17.36
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/13/2012
[/TD]
[TD]2
[/TD]
[TD]3047698
[/TD]
[TD][/TD]
[TD]$52.31
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/13/2012
[/TD]
[TD]2
[/TD]
[TD]3047699
[/TD]
[TD][/TD]
[TD]$38.55
[/TD]
[/TR]
</TBODY>[/TABLE]


This is what my output page looks like:

[TABLE="width: 444"]
<TBODY>[TR]
[TD]Vendor
[/TD]
[TD]Total
[/TD]
[TD]December
[/TD]
[TD]Week1
[/TD]
[TD]Week2
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD="align: right"]$13,284.00
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Thanks in advance! I'm a bit frustrato.

What version of Excel are you using?

 

[andyfox1979]

What version of Excel are you using?

Excel 2010.. i have many more vendors besides ACM, just wanted to use that as a reference for this.

 

[T. Valko]

I'd like to be able to sumif based on a certain criteria (vendor code in my case, here being ACM) plus the week number. In a seperate cell i'd like to sumif by this same criteria plus the entire month. See below:


[TABLE="width: 772"]
<TBODY>[TR]
[TD]Vendor
[/TD]
[TD]Date
[/TD]
[TD]WeekNum
[/TD]
[TD]Invoice #
[/TD]
[TD]PO #
[/TD]
[TD]Amt
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042788
[/TD]
[TD][/TD]
[TD]$146.41
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042789
[/TD]
[TD][/TD]
[TD]$64.29
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042790
[/TD]
[TD][/TD]
[TD]$159.34
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042791
[/TD]
[TD][/TD]
[TD]$80.67
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/5/2012
[/TD]
[TD]1
[/TD]
[TD]3042792
[/TD]
[TD][/TD]
[TD]$141.95
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/6/2012
[/TD]
[TD]1
[/TD]
[TD]3043512
[/TD]
[TD][/TD]
[TD]$17.36
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/13/2012
[/TD]
[TD]2
[/TD]
[TD]3047698
[/TD]
[TD][/TD]
[TD]$52.31
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD]1/13/2012
[/TD]
[TD]2
[/TD]
[TD]3047699
[/TD]
[TD][/TD]
[TD]$38.55
[/TD]
[/TR]
</TBODY>[/TABLE]


This is what my output page looks like:

[TABLE="width: 444"]
<TBODY>[TR]
[TD]Vendor
[/TD]
[TD]Total
[/TD]
[TD]December
[/TD]
[TD]Week1
[/TD]
[TD]Week2
[/TD]
[/TR]
[TR]
[TD]ACM
[/TD]
[TD="align: right"]$13,284.00
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Thanks in advance! I'm a bit frustrato.

With your data on Sheet1 in the range A2:F9...

On Sheet2 A1:E1 = column headers.

You should change the headers Week1 and Week2 to just the number so that it matches the numbers in the weeknum column of the data.

Enter this formula in Sheet2 C2 for the total for the month of December:

=SUMPRODUCT(--(Sheet1!A2:A9=A2),--(TEXT(Sheet1!B2:B9,"mmmm")=C1),Sheet1!F2:F9)

Enter this formula in D2 and copy across to E2:

=SUMIFS(Sheet1!$F$2:$F$9,Sheet1!$A$2:$A$9,$A2,Sheet1!$C$2:$C$9,D$1)

 

[andyfox1979]

With your data on Sheet1 in the range A2:F9...

On Sheet2 A1:E1 = column headers.

You should change the headers Week1 and Week2 to just the number so that it matches the numbers in the weeknum column of the data.

Enter this formula in Sheet2 C2 for the total for the month of December:

=SUMPRODUCT(--(Sheet1!A2:A9=A2),--(TEXT(Sheet1!B2:B9,"mmmm")=C1),Sheet1!F2:F9)

Enter this formula in D2 and copy across to E2:

=SUMIFS(Sheet1!$F$2:$F$9,Sheet1!$A$2:$A$9,$A2,Sheet1!$C$2:$C$9,D$1)

For some reason this formula is really spazzing out my spreadsheet, no idea why. Could you have a look at my spreadsheet and see if it makes more sense? Dont know how to upload, here's a link to it:

http://andyfox.net/Vendors.xlsx

 

[T. Valko]

For some reason this formula is really spazzing out my spreadsheet, no idea why. Could you have a look at my spreadsheet and see if it makes more sense? Dont know how to upload, here's a link to it:

http://andyfox.net/Vendors.xlsx

What does spazzing out mean?

 

[andyfox1979]

What does spazzing out mean?

I put this formula into my excel sheet (and change the ranges, etc) and it keeps freezing up excel and throwing errors. It seems it wants to work, just maybe its how my workbook is designed?

 

[andyfox1979]

Maybe i'm doing it wrong, not sure.

 

[T. Valko]

Maybe i'm doing it wrong, not sure.

Well, it's nothing personal but I'm reluctant to download files from unknown sources.

Out of curiosity, did you change the ranges to be enitre columns?

The SUMPRODUCT formulas are not efficient on entire columns. And, if you have many of them that could slow things down.

 

[andyfox1979]

Well, it's nothing personal but I'm reluctant to download files from unknown sources.

Out of curiosity, did you change the ranges to be enitre columns?

The SUMPRODUCT formulas are not efficient on entire columns. And, if you have many of them that could slow things down.

yes, that's exactly what i did. It is a lot of data so it's really the only way. Is there a better way?