How to SUMIF Values of previous months (current month excluded)

Question

How to SUMIF Values of previous months (current month excluded)

Anonymous

Hi All,

I have 2 Columns of data, 1 for value (i.e. 20, 23, 43, ...) and 1 for date (January, February, March, ...).

Now I need to SUM only those values where the date is before the current month (if today is March, I only want to SUM February, January, December, ...).

I started writing this formula:

=SUMIF(N:N,TEXT(TODAY(),"mmmm"),F:F)

but then I got stuck, I don't know how to exclude the only current month.

Do you have any idea please?

Thanks,

Diego

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

1 additional answer

Answer 1

Anonymous

Hi xDiego49,

I go through the post carefully, you may try formula below and check the result:

=SUM(($B$2:$B$15)*((TODAY()-DAY(TODAY())+1)>$A$2:$A$15))

Best Regards,

Clark

0 comments

Answer 2

HansV 462.6K MVP Volunteer Moderator

I'd use a finite range, and do it like this:

=SUMIFS(F2:F1000, N2:N1000, ">0", N2:N1000, "<"&TODAY()-DAY(TODAY())+1)

The ">0" is used to exclude blank cells in column N.

TODAY()-DAY(TODAY())+1 is the first day of this month, so "<"&TODAY()-DAY(TODAY())+1 means before the first of this month.

0 comments

Answer 3

Rich~M 20,370 Volunteer Moderator

Hi Diego, I am an Excel user like you.

Here is a SUMIF formula to do what you are trying to do:

=SUMIF(N:N,"<"&DATE(YEAR(TODAY()),MONTH(TODAY()),1),F:F)

Reply if you have additional questions or information. Please mark this reply as answered if this solves your question.

Rich~M

0 comments

Share via

How to SUMIF Values of previous months (current month excluded)

1 additional answer