Lookup function/sum function

[Guest]

I have a worksheet (sales orders) that has a list of sales orders that I need
to total up on a different summary worksheet by month. On the sales order
worksheet I have one column that has the sales order date and another that
has the total price. What I want to do is have all the sales added up for
each month on another worksheet. The headers I have on the summary worksheet
are "11/06", "12/06", etc. I want it to just put the summary for each month
it finds on the sales order worksheet under the correct month.

 

[Guest]

Suppose A1 = 11/06 (Date formatted as mm/yy), Sales data is on a tab called
SalesOrders with the dates in A1:A100 and sales in B1:B100:

Try:
=SUMPRODUCT(--(MONTH(SalesOrders!A1:A100)=MONTH(B1)),
--(YEAR(SalesOrders!A1:A100)=YEAR(B1)), SalesOrders!B1:B100)

 

[Domenic]

Assumptions:

Sheet1!A2:A100 contains the sales order date (true date values)

Sheet1!B2:B100 contains the total price

Sheet2!B11 contains the first day of the month and year of interest,
such as November 1, 2005, December 1, 2006, and January 1, 2006 (format
cells as desired)

Formula:

B2, copied across:

=SUMPRODUCT(--(Sheet1!$A$2:$A$100-DAY(Sheet1!$A$2:$A$100)+1=Sheet2!B$1),S
heet1!$B$2:$B$100)

Change the references accordingly.

Hope this helps!

 

[Nick Hodge]

Use a pivot table

Go to data>pivot table chart or report. When asked select Excel data and
select the data you have. Finish the wizard, asking it to place the pivot
table on another sheet.

You should now have a 'shadow' grid. Drag the Date field to the 'Row field'
area and the value field to the 'value' area. Now right click on the date
header in the pivot table and select 'group...'

This will then offer date types to select from, select months and, if the
data is more than one year, select year as well and all should look well.
(As you add data, you will need to extend the range that the data covers, or
set up a dynamic range)

If this makes no sense (It probably doesn't) then read up on pivot tables at
the following

http://www.nickhodge.co.uk/gui/datamenu/pivottablereport.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk

 

[Guest]

That worked perfectly! Thank you!

One follow-up question... Say I want to also have it check another column to
see if there is a certain value in it. For example after it checks the date
have it verify if there is a "1" in column E and then sum just those sales
order totals in column B.
How would I add that to the code you wrote?

 

[Domenic]

Try...

=SUMPRODUCT(--(MONTH(SalesOrders!A1:A100)=MONTH(B1)),
--(YEAR(SalesOrders!A1:A100)=YEAR(B1)),--(SalesOrders!E1:E100=1),SalesOrd
ers!B1:B100)

Hope this helps!

 

[Guest]

Thanks for the help!

Now a follow-up to my follow-up question.

Say I want to use the same approach but sum the sales in B1:B100 using a
prior date to the current month approach. For example if A1 = 11/06, then I
would want it to look up all the sales dates prior to the current month (not
including the current month) and sum the sales in B1:B100. How would I set
that up?

 

[Guest]

If 11/06 is the date 11/1/06 formatted to display as 11/06 then
=SUMPRODUCT(--(SalesOrders!A1:A100<B1), SalesOrders!B1:B100)

If its not 11/1/06 and you have the analysis toolpak installed then
=SUMPRODUCT(--(SalesOrders!A1:A100<=EOMONTH(B1,-1)), SalesOrders!B1:B100)

Or, this should work:
=SUMPRODUCT(--(SalesOrders!A1:A100<DATE(YEAR(B1), MONTH(B1), 1)),
SalesOrders!B1:B100)

 

[Guest]

That worked great! Thank you!

Next question. lol

This might be a bit more trickier.

In column A I have the order date, in column B the order number, in column D
I have the release number. There is a row for each line item of the sales
order. If the line item has multiple releases it will also list those as
well. It also lists the master release which is the entire quantity ordered
but then below that line it will list each release with the individual
quantites for those releases. So if I add up all the line items for a
specific order then it will actually be double the amount since the master
list is the total quantity. What I want to do is use the same formula you
gave me before but have it look if an order has muliple releases. If it does
then have it add up only the release numbers greater than 0 but also add up
the orders that do not have multipl releases. These would only have a 0 in
the column d. Example:

Column A Column B Column C Column D
11/01/06 217646 $100.00 0 (master release of this order)
11/01/06 217646 $50.00 1 (first release)
11/01/06 217646 $50.00 2 (second release)
11/01/06 216554 $100.00 0 (single release)
12/01/06 321222 $200.00 0 (single release)

So what I want to do is look up all the sales for each month like I did from
your last formula but only add up the sales that have 1 release (column d is
0), and add only the sales that have multiple releases but not use the main
release of 0 for that order.
I hope this makes sense to you.

I greatly appreciate all your help with this.

SS

 

[Guest]

Assuming your date 11/06 is in cell F1 and the subsequent releases could be
in a different month from the master release (if the order number appears
once, look for a 0 in column D and if the order number appears more than
once, look for a number greater than 0 in column D), this appeared to work
=SUMPRODUCT(--(MONTH(A1:A5)=MONTH(F1)),--(YEAR(A1:A5)=YEAR(F1)),--(D15>=(--(COUNTIF(B1:B5,B1:B5)>1))),C1:C5)

array entered w/Cntrl+Shift+Enter

If the master release and the later releases are all in the same month you
could just test column D for 0 (just add the master releases - which would
work for the sample data you've given):
=SUMPRODUCT(--(MONTH(A1:A5)=MONTH(F1)),--(YEAR(A1:A5)=YEAR(F1)),--(D15=0),C1:C5)


entered normally

 

[Guest]

I believe your first formula will work but what about the orders that are in
the list multiple times and don't have multiple releases? If they have more
than one line item on the order it will also have a 0 in column D.

For order 217646 I want it to only add the 1 & 2 releases for Item 1 and
also add Item 2 for order 217646 since it is a single release. For all the
others I want it to add everything since they have no multiple release line
items.

 

[Guest]

I do have another column that has the line item if that helps with the
formula. Call column E the line item column.

 

[Guest]

Personally, I would use a different identifier for a master release (or set
up another column to identify the master release). Otherwise, the only way
to pull out the master release is if there are multiple order numbers w/the
same item description and the cell is zero. A master release will have a
release number of 0, but a 0 doesn't necessarily mean the entry is a master
release - I try to avoid setups like this.

Let's say "M" is for a master release. Then I would use (where G1 = 11/06):
=SUMPRODUCT(--(MONTH(A1:A7)=MONTH(G1)),--(YEAR(A1:A7)=YEAR(G1)),--(D17<>"M"),C1:C7)

My second choice would be to set up a helper column in F and concatenate the
order and item numbers
=B1&E1

then try
=SUMPRODUCT(--(MONTH(A1:A7)=MONTH(G1)),--(YEAR(A1:A7)=YEAR(G1)),--(D17>=(--(COUNTIF(F1:F7,F1:F7)>1))),C1:C7)

array entered w/Cntrl+Shift+Enter

 

[Guest]

I do have another column on my worksheet that has a value of "True" or
"False". If the line item has no multiple releases then the value is set to
"False". If the line item has multiple releases the value for the master line
item and the releases has a value of "True". Could we use that to say if the
value is "false" then add the values in column C regardless of what the value
is in column D, and if it's "True" then have it only add the values in column
C if the value in Column D is greater than 0?

I agree about avoiding setups like this but I'm extracting this data from
our MRP system and I'm trying to use what the system is giving me.

 

[Guest]

So we wouldn't need to put column B in the equation at all. Right?

 

[Guest]

Where G1 = 11/06 and F1:F7 is your True/False values identify the entries
w/master releases, try

=SUMPRODUCT(--(MONTH(A1:A7)=MONTH(G1)),--(YEAR(A1:A7)=YEAR(G1)),--(D17>=(--F1:F7)),C1:C7)

The month/year are still important right? You are not just trying to sum up
a particular item regardless of the month in which it was released?
That would be, where G1 = 217646
=SUMPRODUCT(--(B1:B7=G1),--(D17>=(--F1:F7)),C1:C7)

 

[Guest]

I believe that worked! I haven't tested it fully but from what I can tell it
seems to be working fine. You'll be the first to know if I have a problem.

Thanks for all your help. I greatly appreciate your time and effort. It's
nice to find people out there that are willing to help like this.

SS

 

[Guest]

Glad to help. Thanks for the feedback.

I believe that worked! I haven't tested it fully but from what I can tell it
seems to be working fine. You'll be the first to know if I have a problem.

Thanks for all your help. I greatly appreciate your time and effort. It's
nice to find people out there that are willing to help like this.

SS

 

[Guest]

Hi there!

I found one small problem that I think I need to add to this formula. Since
we are pulling these amounts based on the column that has the true or false
value along with the release number column I now need to add a piece that
says if it finds a true value in coulmn F and only finds a 0 in columnd D
then add the values in column C for that release. From what I was told there
can be instances where that value can be true and still have only a 0 for the
master release column and no other releases based on that line item.

Hope I made sense again.

SS

 

[Guest]

As long as the master releases will always precede the later releases, where
I1 = 11/06, A1:A7 is the date, B1:B7 is the order number, C1:C7 is the
amount, E1:E7 is the item number (ie "Item 1", "Item 2"), try

=SUMPRODUCT(--(MONTH(A1:A8)=MONTH(I1)),--(YEAR(A1:A8)=YEAR(I1)),--(FREQUENCY(MATCH(B1:B7&E1:E7,B1:B7&E1:E7,0),MATCH(B1:B7&E1:E7,B1:B7&E1:E7,0))<=1),C1:C8)

This should exclude only master releases that have a later release. Of
course, if those subsequent releases are in a differenct month - they will
not be included.

Also note that although the data is in rows 1-7, I am referencing rows 1-8
(for formulas outside of the frequency function) which is necessary because
the frequency function always returns n+1 elements (and the arrays must all
be same size or you get errors).

If that doesn't work, I think you'll have to add some helper cells. At
least something that will identify items that belong to the same "group".