sumif

[esrei]

Every week I need to do a summary of sales for the week.
I have a list of product sold in one week, and every week
uses a sumif formula to get te totals for one week.

If I have:

Col.A Week18
Col.B Product code
Col.C Qty sold

Is there a formula that I can add to state that if
Col.A's value changes the range for the new formula must
change to the new weeks range.

 

[Jerry W. Lewis]

You have not adequately described your data layout or formulas.

I will guess that you normally have a fixed number of rows for a given
week, and are using SUMIF() to select specific product codes within that
fixed number of rows. You can use SUMPRODUCT to sum based on two
criteria (week and product) while referencing a much larger area.

The general form (absent enough information to write a specific formula) is
=SUMPRODUCT((condition1)*(condition2)*sumrange)

Jerry

 

[Don Guillett]

to expand on this a bit
The general form (absent enough information to write a specific formula) is

=SUMPRODUCT((condition1)*(condition2)*sumrange)

=SUMPRODUCT((rngA=condition1)*(rngB=condition2)*rngCsumrange)
the ranges must be the same size ie a2:a200, b2:b200