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

cdhamo

New member
Joined
Apr 27, 2013
Messages
13
Reaction score
0
Points
0
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.
 

Attachments

  • Help.xlsx
    31.2 KB · Views: 21

Canapone

New member
Joined
Oct 3, 2011
Messages
99
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
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
 

cdhamo

New member
Joined
Apr 27, 2013
Messages
13
Reaction score
0
Points
0
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:eek:hwell:
 

Canapone

New member
Joined
Oct 3, 2011
Messages
99
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
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
 

Attachments

  • ciao.xlsx
    32 KB · Views: 15

cdhamo

New member
Joined
Apr 27, 2013
Messages
13
Reaction score
0
Points
0
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.
 

Attachments

  • Help 2.xlsx
    33.6 KB · Views: 11

Canapone

New member
Joined
Oct 3, 2011
Messages
99
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
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
 

Canapone

New member
Joined
Oct 3, 2011
Messages
99
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
Excel 2010
Hi again,

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

Hope it helps
 

Attachments

  • ciao2.xlsx
    33.7 KB · Views: 10

cdhamo

New member
Joined
Apr 27, 2013
Messages
13
Reaction score
0
Points
0
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.
 

cdhamo

New member
Joined
Apr 27, 2013
Messages
13
Reaction score
0
Points
0
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
Joined
Oct 3, 2011
Messages
99
Reaction score
0
Points
0
Location
Italy
Excel Version(s)
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
 

cdhamo

New member
Joined
Apr 27, 2013
Messages
13
Reaction score
0
Points
0
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

EXCELLENT - perfect - thank you so much for all your help.
 
Last edited:
Top