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

#### cdhamo

##### New member
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
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
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 helphwell:

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

#### Attachments

• ciao.xlsx
32 KB · Views: 15

#### cdhamo

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

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
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
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
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
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

#### cdhamo

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

EXCELLENT - perfect - thank you so much for all your help.

Last edited:

#### cdhamo

##### New member
Canapone - can you please send me a PM?