Sum based on Date and Year number

Pavi

New Member
Joined
Dec 2, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Dear Excel Gurus,

I am trying to sum with Sumifs based on the date.

TypeDateYear
Apple01.01.20202
Apple01.05.20202
Mango01.01.20203
Mango01.01.20193
Apple01.01.20192
Mango01.01.20183
Banana01.01.20185

please note data is example but in actual list is from last 5 years.

I am looking for Sum based on date an year condition.
For example: Sum only all Apple from current date to last 2 year only if year is 2. same Sum only Mango from current date or month to last 3 year is year is 3... soon.
looking for support.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi Pavi,

I'm a little confused by your question.
  1. You say SUMIFS but it's unclear what you are summing? If it's the Year then my example should work but I'm thinking you may mean COUNTIFS?
  2. I don't understand "from current date or month" so I'm giving 2 results. Result 1 is from current date and Result 2 is from 1st of current month.
  3. I note each fruit has a duplicate year entry so I am assuming I can use the first occurrence of the specified fruit to ascertain how may years to look back.
Please also note this will not work if today is 29 February as there will be no 29th February two years previously. Let me know if that's an issue.

Book1
ABCDEFG
1FruitDateYearFruitResult 1Result 2
2Apple01-Jan-202Mango99
3Apple05-Jan-202
4Mango01-Jan-203
5Mango01-Jan-193
6Apple01-Jan-192
7Mango01-Jan-183
8Banana01-Jan-185
Sheet1
Cell Formulas
RangeFormula
F2F2=SUMIFS($C$2:$C$9999,$B$2:$B$9999,">="&DATE(YEAR(TODAY())-INDEX($C$2:$C$9999,MATCH(E2,$A$2:$A$9999,0)),MONTH(TODAY()),DAY(TODAY())),$A$2:$A$9999,E2)
G2G2=SUMIFS($C$2:$C$9999,$B$2:$B$9999,">="&DATE(YEAR(TODAY())-INDEX($C$2:$C$9999,MATCH(E2,$A$2:$A$9999,0)),MONTH(TODAY()),1),$A$2:$A$9999,E2)
 
Upvote 0
thank you so much for reply.

I forgot to add column in example which is units and this needs to be sum based on the Date and year.


TypeDateYearUnits
Apple01.01.2020210
Apple01.05.202025
Mango01.01.20203100
Mango01.01.20193200
Apple01.01.201925
Mango01.01.2018310
Banana01.01.2018520
 
Upvote 0
Is this what you want?

Pavi.xlsx
ABCDEFG
1TypeDateYearUnitsFruitResult
2Apple01-Jan-20210Mango310
3Apple05-Jan-2025
4Mango01-Jan-203100
5Mango01-Jan-193200
6Apple01-Jan-1925
7Mango01-Jan-18310
8Banana01-Jan-18520
Sheet2
Cell Formulas
RangeFormula
G2G2=SUMIFS($D$2:$D$9999,$B$2:$B$9999,">="&DATE(YEAR(TODAY())-INDEX($C$2:$C$9999,MATCH(F2,$A$2:$A$9999,0)),MONTH(TODAY()),DAY(TODAY())),$A$2:$A$9999,F2)
 
Upvote 0
this is exactly I was looking, thank you so much... although I don't understand the logic.
 
Upvote 0
Let me break it down:

SUMIFS($D$2:$D$9999
so this sums column D based upon criteria.

Criteria 1
,$B$2:$B$9999,">="
The date is greater than or equal to

&DATE(YEAR(TODAY())-
current year minus

INDEX($C$2:$C$9999,MATCH(F2,$A$2:$A$9999,0)),
the first year from column C where the fruit matches the selected fruit in column A

MONTH(TODAY()),DAY(TODAY())),
with the current month and day

Criteria 2
$A$2:$A$9999,F2)
and the Fruit matches the selected fruit in F2
 
Upvote 0
Sumproduct is also an alternative
T202012a.xlsm
ABCDEFGHI
1
2TypeDateYearUnitsFruitResult
3Apple1-Jan-20202010
4Apple1-May-2020205Mango310
5Mango1-Jan-202020100StartEnd
6Mango1-Jan-1920192001-Jan-1931-Dec-20Mango300
7Apple1-Jan-19201951-Jan-1831-Dec-18Mango10
8Mango1-Jan-18201810
9Banana1-Jan-18201820Year
102018Mango10
11Years =>
122019Mango300
1f
Cell Formulas
RangeFormula
I4I4=SUMPRODUCT(--(Type=H4),Units)
I6:I7I6=SUMPRODUCT(--(Type=H6),--(Date>=F6),--(Date<G6), Units)
I10I10=SUMPRODUCT(--(Type=H10),--(Year=G10), Units)
I12I12=SUMPRODUCT(--(Type=H12),--(Year>=G12),Units)
Named Ranges
NameRefers ToCells
Date='1f'!$B$3:$B$9I6:I7
Type='1f'!$A$3:$A$9I12, I10, I4, I6:I7
Units='1f'!$D$3:$D$9I12, I10, I4, I6:I7
Year='1f'!$C$3:$C$9I12, I10
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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