Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Need help with indexing, matching or sumproduct to find date ranges

  1. #1

    Need help with indexing, matching or sumproduct to find date ranges



    Register for a FREE account, and/
    or Log in to avoid these ads!

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

  2. #2
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Excel Version
    Excel 2010
    Hi,

    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

  3. #3
    Quote Originally Posted by Canapone View Post
    Hi,

    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

  4. #4
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Excel Version
    Excel 2010
    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
    Attached Files Attached Files

  5. #5
    Quote Originally Posted by Canapone View Post
    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
    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.
    Attached Files Attached Files

  6. #6
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Excel Version
    Excel 2010
    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

  7. #7
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Excel Version
    Excel 2010
    Hi again,

    attached an example where i'm cutting off "capital" values

    Hope it helps
    Attached Files Attached Files

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

  9. #9
    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 by cdhamo; 2013-04-28 at 10:32 AM.

  10. #10
    Acolyte Canapone's Avatar
    Join Date
    Oct 2011
    Location
    Italy
    Posts
    97
    Articles
    0
    Excel Version
    Excel 2010
    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

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •