SUMIF/SUMPRODUCT Between Dates
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. |
SUMIF/SUMPRODUCT Between Dates
"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 |
All times are GMT +1. The time now is 03:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com