sumifs or sumproduct with the month and year given on different cell

mark692

Active Member
Joined
Feb 27, 2015
Messages
306
Office Version
  1. 2016
Platform
  1. Windows

on cell f7 on ISBS sheet i want to put a formula that will give me the sum of column D if the year and month is same on cell f5 and cell F6 of ISBS sheet


Excel 2013/2016
F
2
3
4
52018
6January
7

<tbody>
</tbody>
IS BS

Excel 2013/2016
BCD
16date17-Oct Acquisition useful life 60 months Total Machineries Depreciation
1710/30/2017 12,143.46 12,143.46
1811/30/2017 12,143.46 12,143.46
1912/30/2017 12,143.46 12,143.46
2001/30/2018 12,143.46 12,143.46
2102/28/2018 12,143.46 12,143.46
2203/30/2018 12,143.46 12,143.46
2304/30/2018 12,143.46 12,143.46
2405/30/2018 12,143.46 12,143.46
2506/30/2018 12,143.46 12,143.46
2607/30/2018 12,143.46 12,143.46
2708/30/2018 12,143.46 12,143.46
2809/30/2018 12,143.46 12,143.46
2910/30/2018 12,143.46 12,143.46
3011/30/2018 12,143.46 12,143.46
3112/30/2018 12,143.46 12,143.46
3201/30/2019 12,143.46 12,143.46

<tbody>
</tbody>
Lapsing Schedule
 
Last edited:

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,464
Office Version
  1. 365
Platform
  1. Windows
Hi, you could try:

=SUMIFS($D$17:$D$32,$B$17:$B$32,">="&F6&F5,$B$17:$B$32,"<="&EOMONTH(F6&F5,0))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
It would be helpful if you also had posted the expected sum value.

In F7 enter:

=SUMIFS(D17:D32,B17:B32,">="&1&F6&F5,B17:B32,"<="&EOMONTH(1&F6&F5,0))
 

mark692

Active Member
Joined
Feb 27, 2015
Messages
306
Office Version
  1. 2016
Platform
  1. Windows
the expected sum value is 12,143.46 from D20 because the date on B20 is january 30 2018


BTW thanks guys your formula work!!

 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,604
Members
414,081
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top