SOLVED

Sumif prior to current month

Brass Contributor

Hi there,

 

Please see image below. I am trying to have column B sum if the month in row 2 is prior to current month today. Example it's currently April so I want it to sum only till April and not include anything past April. I know there is a formula for when it's an actual date but not when it's a whole month. Any help is greatly appreciated! Thank you!!

 

LisaMarie1981_0-1681775341609.png

 

3 Replies
best response confirmed by LisaMarie1981 (Brass Contributor)
Solution

Hi @LisaMarie1981 

 

(next times please provide Excel version + Operating system => Welcome to your Excel discussion space!)

 

Sample.png

 

in B3:

 

=IF(MONTH(TODAY())=1, 0, SUM(OFFSET(B3,,1,,MATCH(TEXT(TODAY(),"MMMM"),C$2:N$2,0)-1)))

 

Other option with Excel 2021/365:

=IF(
  MONTH(TODAY())=1, 0,
  SUM(C3:XLOOKUP(TEXT(EDATE(TODAY(),-1),"MMMM"),C$2:N$2,C3:N3))
)
Amazing, thank you so much!!
Glad you have a solution & Thanks for providing feedback
1 best response

Accepted Solutions
best response confirmed by LisaMarie1981 (Brass Contributor)
Solution

Hi @LisaMarie1981 

 

(next times please provide Excel version + Operating system => Welcome to your Excel discussion space!)

 

Sample.png

 

in B3:

 

=IF(MONTH(TODAY())=1, 0, SUM(OFFSET(B3,,1,,MATCH(TEXT(TODAY(),"MMMM"),C$2:N$2,0)-1)))

 

Other option with Excel 2021/365:

=IF(
  MONTH(TODAY())=1, 0,
  SUM(C3:XLOOKUP(TEXT(EDATE(TODAY(),-1),"MMMM"),C$2:N$2,C3:N3))
)

View solution in original post