Here's what I'm trying to do and not sure how to achieve it.

Sheet1
Col A = Project Number (5 digit text)
Col B = Date of Spending (Display format is MMM-YY)
Col C = Spending Amount (Accounting format)

Sheet2
Col A = Project Number (5 digit text)
Col B = where I need formula

The formula in sheet2 should be if Proj Sheet 1=Proj Sheet 2 and Date Sheet1
ColB = jan-06 then sum Sheet1 Spend

I think part of my problem is that the date is stored as MM/DD/YYYY so the
date could be any number of days within the month.

Here's what i've got so far and it's giving me an error of #NAME?

=SUMPRODUCT(--('Sheet1'!A2:A9999='Sheet2'!A2),--('Sheet1'!B2:B9999="Jan-06"),'Sheet2'!C2:C9999)

Thanks in advance for assistance.

"Marlo" wrote...
Here's what I'm trying to do and not sure how to achieve it.

Sheet1
Col A = Project Number (5 digit text)
Col B = Date of Spending (Display format is MMM-YY)
Col C = Spending Amount (Accounting format)

Sheet2
Col A = Project Number (5 digit text)
Col B = where I need formula

The formula in sheet2 should be if Proj Sheet 1=Proj Sheet 2 and Date
Sheet1
ColB = jan-06 then sum Sheet1 Spend

I think part of my problem is that the date is stored as MM/DD/YYYY so the
date could be any number of days within the month.

Here's what i've got so far and it's giving me an error of #NAME?

=SUMPRODUCT(--('Sheet1'!A2:A9999='Sheet2'!A2),--('Sheet1'!B2:B9999="Jan-06"),'Sheet2'!C2:C9999)

Thanks in advance for assistance.

Hi!

Try this:

=SUMPRODUCT(--(Sheet1!A2:A9999=Sheet2!A2),--(TEXT(Sheet1!B2:B9999,"mmm-yy")="Jan-06"),Sheet2!C2:C9999)

Biff