Lookup function/sum function

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.

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.

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!

In article ,
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.

Use a pivot table

Go to datapivot 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/datam...ablereport.htm

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

HIS
www.nickhodge.co.uk

"Secret Squirrel" wrote in
message news
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.

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.

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.

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?

"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.

If 11/06 is the date 11/1/06 formatted to display as 11/06 then
=SUMPRODUCT(--(SalesOrders!A1:A100B1), 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:A100DATE(YEAR(B1), MONTH(B1), 1)),
SalesOrders!B1:B100)



"Secret Squirrel" wrote:

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?

"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.

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.

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

"Secret Squirrel" wrote:

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.