SUMIF with Multiple Conditions

[TexJen09]

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!

 

[Max]

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]

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

 

[TexJen09]

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]

Welcome, glad you got it up & working fine over there !