SUMIF with Multiple Conditions
 

I'm new to the community here so bare with me if my lingo isn't up to par
just yet. I'm in Excel 2003.
My workbook has 2 sheets; 'Inventory' and 'Source'.

'Inventory' has:
Month SKU# Beginning Inventory Ending Inventory
Jan '08 555 50,000
444 35,000
333 40,000

'Source' has:
Order Date SKU# Quantity
01/13/08 555 25,000
01/22/08 444 10,000
01/22/08 444 3,000
01/22/08 333 5,000
01/31/2008 555 3,000
02/13/08 555 2,000
02/22/08 333 1,500

I'm needing to provide the 'Ending Inventory' to the "Inventory" worksheet,
using the beginning quantity (in 'Inventory'), subtracting the total quantity
for each month (In 'Source'), by SKU #.

In my terms, the Ending Inventory for Jan '08, for SKU # 555 above would be:
Beginning Inventory shows 50,000
If SKU = 555 & Date is between 01/01/2008:01/31/2008 = 28,000
If SKU = 444 & Date is between 01/01/2008:01/31/2008 = 13,000

It's the 'between this date range' that is throwing me off and then coupling
it with the SKU #s.

Any direction is much appreciated. Truly!
thnx!

SUMIF with Multiple Conditions
 

In Inventory,
Assuming that A2 contains the *text*: Jan '08
Place this in D2 (under "Ending Inventory"):
=SUMPRODUCT((Source!$B$2:$B$8=B2)*(TEXT(Source!$A$ 2:$A$8,"mmm
'yy")=$A$2),Source!$C$2:$C$8)
Copy D2 down to return required results for each SKU#
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TexJen09" wrote:
I'm new to the community here so bare with me if my lingo isn't up to par
just yet. I'm in Excel 2003.
My workbook has 2 sheets; 'Inventory' and 'Source'.

'Inventory' has:
Month SKU# Beginning Inventory Ending Inventory
Jan '08 555 50,000
444 35,000
333 40,000

'Source' has:
Order Date SKU# Quantity
01/13/08 555 25,000
01/22/08 444 10,000
01/22/08 444 3,000
01/22/08 333 5,000
01/31/2008 555 3,000
02/13/08 555 2,000
02/22/08 333 1,500

I'm needing to provide the 'Ending Inventory' to the "Inventory" worksheet,
using the beginning quantity (in 'Inventory'), subtracting the total quantity
for each month (In 'Source'), by SKU #.

In my terms, the Ending Inventory for Jan '08, for SKU # 555 above would be:
Beginning Inventory shows 50,000
If SKU = 555 & Date is between 01/01/2008:01/31/2008 = 28,000
If SKU = 444 & Date is between 01/01/2008:01/31/2008 = 13,000

It's the 'between this date range' that is throwing me off and then coupling
it with the SKU #s.

Any direction is much appreciated. Truly!
thnx!

SUMIF with Multiple Conditions
 

Hi Max -
I was away on holiday for a few and am picking this back up now. Thank you
so much for your help. after looking at that formula, I can see why I was
having a hard time with it.
You are correct that A2 contains *text*: Jan '08.
I entered the formula as you have below into D2 and I'm generating 0 for all
Ending Inventory.
I have no doubt I have left something out of my explanation or made a
mistake on my end.
I have copied the formula to insure I did not have a typo in it.
It is generting a value, which is better than where I got, but it's only
generating a 0 for all Ending Inventory.

I'm not sure how to go about troubleshooting this from here.
Is it possible for me to send you the file or do you have another
suggestion? Sorry, I'm new to this and not sure what protocol is proper.

Thanks so much again for any additional assistance. I love Excel and am
eager to learn more.
- TexJen09

"Max" wrote:

In Inventory,
Assuming that A2 contains the *text*: Jan '08
Place this in D2 (under "Ending Inventory"):
=SUMPRODUCT((Source!$B$2:$B$8=B2)*(TEXT(Source!$A$ 2:$A$8,"mmm
'yy")=$A$2),Source!$C$2:$C$8)
Copy D2 down to return required results for each SKU#
Adapt the ranges to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TexJen09" wrote:
I'm new to the community here so bare with me if my lingo isn't up to par
just yet. I'm in Excel 2003.
My workbook has 2 sheets; 'Inventory' and 'Source'.

'Inventory' has:
Month SKU# Beginning Inventory Ending Inventory
Jan '08 555 50,000
444 35,000
333 40,000

'Source' has:
Order Date SKU# Quantity
01/13/08 555 25,000
01/22/08 444 10,000
01/22/08 444 3,000
01/22/08 333 5,000
01/31/2008 555 3,000
02/13/08 555 2,000
02/22/08 333 1,500

I'm needing to provide the 'Ending Inventory' to the "Inventory" worksheet,
using the beginning quantity (in 'Inventory'), subtracting the total quantity
for each month (In 'Source'), by SKU #.

In my terms, the Ending Inventory for Jan '08, for SKU # 555 above would be:
Beginning Inventory shows 50,000
If SKU = 555 & Date is between 01/01/2008:01/31/2008 = 28,000
If SKU = 444 & Date is between 01/01/2008:01/31/2008 = 13,000

It's the 'between this date range' that is throwing me off and then coupling
it with the SKU #s.

Any direction is much appreciated. Truly!
thnx!

SUMIF with Multiple Conditions
 

.. but it's only generating a 0 for all Ending Inventory.
Think you got hit with an inadvertent line break when you copied from the
post n pasted the formula in D2's formula bar

This TEXT part below should be corrected to appear as:
.... *(TEXT(Source!$A$2:$A$8,"mmm 'yy")=$A$2)
The formula will work fine once you correct it to the above

Anyway, here's a link to a sample to illustrate it working the way it should:
http://www.freefilehosting.net/download/3dgkk
multicriteria sum.xls

Take a moment to press the "Yes" button below to the question: "Was this
post helpful to you?" from where you're reading this. It'll ensure a longer
shelf life to this thread for the general benefit of other readers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TexJen09" wrote:
Hi Max -
I was away on holiday for a few and am picking this back up now. Thank you
so much for your help. after looking at that formula, I can see why I was
having a hard time with it.
You are correct that A2 contains *text*: Jan '08.
I entered the formula as you have below into D2 and I'm generating 0 for all
Ending Inventory.
I have no doubt I have left something out of my explanation or made a
mistake on my end.
I have copied the formula to insure I did not have a typo in it.
It is generting a value, which is better than where I got, but it's only
generating a 0 for all Ending Inventory.

I'm not sure how to go about troubleshooting this from here.
Is it possible for me to send you the file or do you have another
suggestion? Sorry, I'm new to this and not sure what protocol is proper.

Thanks so much again for any additional assistance. I love Excel and am
eager to learn more.
- TexJen09

SUMIF with Multiple Conditions
 

PERFECTION! I figured out my problem was that I didn't have *text* for the
months in the Inventory sheet, so I changed it to text and all is well.
And, I was able to expand my range to accommodate more rows and add more
months - - I was able to alter the formula sucessfully.

THANK YOU SO MUCH!!!
-TexJen09

"Max" wrote:

.. but it's only generating a 0 for all Ending Inventory.
Think you got hit with an inadvertent line break when you copied from the
post n pasted the formula in D2's formula bar

This TEXT part below should be corrected to appear as:
... *(TEXT(Source!$A$2:$A$8,"mmm 'yy")=$A$2)
The formula will work fine once you correct it to the above

Anyway, here's a link to a sample to illustrate it working the way it should:
http://www.freefilehosting.net/download/3dgkk
multicriteria sum.xls

Take a moment to press the "Yes" button below to the question: "Was this
post helpful to you?" from where you're reading this. It'll ensure a longer
shelf life to this thread for the general benefit of other readers.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TexJen09" wrote:
Hi Max -
I was away on holiday for a few and am picking this back up now. Thank you
so much for your help. after looking at that formula, I can see why I was
having a hard time with it.
You are correct that A2 contains *text*: Jan '08.
I entered the formula as you have below into D2 and I'm generating 0 for all
Ending Inventory.
I have no doubt I have left something out of my explanation or made a
mistake on my end.
I have copied the formula to insure I did not have a typo in it.
It is generting a value, which is better than where I got, but it's only
generating a 0 for all Ending Inventory.

I'm not sure how to go about troubleshooting this from here.
Is it possible for me to send you the file or do you have another
suggestion? Sorry, I'm new to this and not sure what protocol is proper.

Thanks so much again for any additional assistance. I love Excel and am
eager to learn more.
- TexJen09

SUMIF with Multiple Conditions
 

Welcome, glad you got it up & working fine over there !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"TexJen09" wrote in message
...
PERFECTION! I figured out my problem was that I didn't have *text* for
the
months in the Inventory sheet, so I changed it to text and all is well.
And, I was able to expand my range to accommodate more rows and add more
months - - I was able to alter the formula sucessfully.

THANK YOU SO MUCH!!!
-TexJen09