Lookup function/sum function
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
"Domenic" wrote:
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!
In article ,
Secret Squirrel wrote:
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?
"JMB" wrote:
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)
"Secret Squirrel" wrote:
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.
|