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]

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?

If you're using Excel 2010 then that's 1048576 rows per reference. Do you have data in all 1048576 rows?

If not, then using entire columns as range references with a SUMPRODUCT function will adversely affect performance.

We can probably use a SUMIFS function instead of SUMPRODUCT.

I'm going to be away for a few hours but I'll get back to you.

 

[T. Valko]

If you're using Excel 2010 then that's 1048576 rows per reference. Do you have data in all 1048576 rows?

If not, then using entire columns as range references with a SUMPRODUCT function will adversely affect performance.

We can probably use a SUMIFS function instead of SUMPRODUCT.

I'm going to be away for a few hours but I'll get back to you.

Let's redo this formula:

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

You'll have to change the header in C1 to be the 1st of the month date: 12/1/2012. You can still format the cell to display the month name.

Then, the formula would be:

=SUMIFS(Sheet1!F2:F9,Sheet1!A2:A9,A2,Sheet1!B2:B9,">="&C1,Sheet1!B2:B9,"<="&EOMONTH(C1,0))

The SUMIFS function is more efficient compared to the SUMPRODUCT function but the SUMPRODUCT function is much more versatile compared to the SUMIFS function.