Hi all. My knowledge of Excel is basic to intermediate, and I'm trying to find a solution to my spreadsheet. Any assistance would be appreciated. On the second worksheet, I need to search columns A(calendar month range),E, then sum F. I would like this sum to transfer over to the first spreadsheet. I've tried a productsum formula, but it is only picking up the 1st day of the month, not the whole calendar month. Thanks.
Need help with indexing, matching or sumproduct to find date ranges
- Thread starter cdhamo
- Start date
Canapone
New member
Hi,
try to modify first segment of sumproduct in order to match MONTH of D4 to MONTH of Income!A4:A30
Hope it helps
try to modify first segment of sumproduct in order to match MONTH of D4 to MONTH of Income!A4:A30
Code:
=SUMPRODUCT((MONTH(D$4)=MONTH(Income!$A$4:$A$30))*($B6=Income!$E$4:$E$30)*(Income!$F$4:$F$30))Hope it helps
Sorry that didn't seem to help. Thank you for assisting. Any other ideas? I was thinking about searching for a date range using the next month on the P&L... Again, I think I'll need some help
hwell:Canapone
New member
Hi,
not sure to have understood. I've attached a file with the formulas. Could you kindly upload a file with expected results?
Meanwhile I hope you could get other helps and ideas from friends of the Forum.
Regards
not sure to have understood. I've attached a file with the formulas. Could you kindly upload a file with expected results?
Meanwhile I hope you could get other helps and ideas from friends of the Forum.
Regards
Thank you so much! Your a gem! It's now working for me. I've got one more formula I need assistance with. I hope you can help me? I've attached the file called Help 2.
Canapone
New member
Hi, thanks - first of all- for your kind feedback.
In the formula
=(sumproduct((ExpDate>=B2)*(ExpDate<=C2)*(ExpAmount);ALLEXCEPT(CapitalforBAS)))
the segment
=sumproduct((ExpDate>=B2)*(ExpDate<=C2)*(ExpAmount))
is Ok.
What would you need to exclude?
Example
=sumproduct((ExpDate>=B2)*(ExpDate<=C2)*ExpAmount*(CapitalforBAS<>"General Expenses"))
It excludes General Expenses.
I'd need an example
Regards
In the formula
=(sumproduct((ExpDate>=B2)*(ExpDate<=C2)*(ExpAmount);ALLEXCEPT(CapitalforBAS)))
the segment
=sumproduct((ExpDate>=B2)*(ExpDate<=C2)*(ExpAmount))
is Ok.
What would you need to exclude?
Example
=sumproduct((ExpDate>=B2)*(ExpDate<=C2)*ExpAmount*(CapitalforBAS<>"General Expenses"))
It excludes General Expenses.
I'd need an example
Regards
Hi. I've entered the following formula, but it's not working - shows $2. =(SUMPRODUCT((ExpDate>=B2)*(ExpDate<=C2)*(ExpAmount<>CapitalforBAS))) Sorry, I didn't explain... I want to search in first worksheet in date range from second sheet, suming all of F column, excluding any descriptions matching "Capital" (which is CapitalforBAS) in E column.
I also have another range on a sheet that isn't included in your example, called "capital". This is where the original dropdown box in column E comes from. Sorry hope this makes sense. I have tried this =SUMPRODUCT((ExpDate>=B2)*(ExpDate<=C2)*ExpAmount*(MATCH,CapitalforBAS,Expenses)) but not working.
Last edited:
Canapone
New member
Hi, just an attempt
the segment
(CapitalforBAS<>"Capital")
inside SUMPRODUCT, exceludes Capital, but I did not understand the "grammar" of last segment of the new formula
(MATCH,CapitalforBAS,Expenses)
regards
the segment
(CapitalforBAS<>"Capital")
inside SUMPRODUCT, exceludes Capital, but I did not understand the "grammar" of last segment of the new formula
(MATCH,CapitalforBAS,Expenses)
regards
EXCELLENT - perfect - thank you so much for all your help.
Last edited: