Lookup function/sum function

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.

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

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.

"JMB" wrote:

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.

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

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


"JMB" wrote:

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.

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


"Secret Squirrel" wrote:

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.

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

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.

"JMB" wrote:

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.

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.


"JMB" wrote:

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


"Secret Squirrel" wrote:

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.

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

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.

"JMB" wrote:

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.

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

"JMB" wrote:

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


"Secret Squirrel" wrote:

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.

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

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.

"JMB" wrote:

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.

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)





"Secret Squirrel" wrote:

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.


"JMB" wrote:

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


"Secret Squirrel" wrote:

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.

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

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.

"JMB" wrote:

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.

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

"JMB" wrote:

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)





"Secret Squirrel" wrote:

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.


"JMB" wrote:

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


"Secret Squirrel" wrote:

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.

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

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.

"JMB" wrote:

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.

Glad to help. Thanks for the feedback.

"Secret Squirrel" wrote:

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

"JMB" wrote:

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)





"Secret Squirrel" wrote:

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.


"JMB" wrote:

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


"Secret Squirrel" wrote:

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.

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

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.

"JMB" wrote:

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.

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

"JMB" wrote:

Glad to help. Thanks for the feedback.

"Secret Squirrel" wrote:

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

"JMB" wrote:

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)





"Secret Squirrel" wrote:

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.


"JMB" wrote:

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


"Secret Squirrel" wrote:

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.

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

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.

"JMB" wrote:

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.

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


"Secret Squirrel" wrote:

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

"JMB" wrote:

Glad to help. Thanks for the feedback.

"Secret Squirrel" wrote:

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

"JMB" wrote:

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)





"Secret Squirrel" wrote:

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.


"JMB" wrote:

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


"Secret Squirrel" wrote:

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.

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

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.

"JMB" wrote:

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.