SUMIF with Different Dates

Katich

Board Regular
Joined
Jan 22, 2008
Messages
112
Hello,

I'm having problems with getting my sumif to work how i need it to. My current project has me summing values in a column based on a date range. So i need to fill in the invoice amount cell (B2) based on the Month (B1). In column C, there will be a variety of dates which i will want to sum the totals based on what Month i have in B1. There will be a drop down list of all the months in B1 so that i can select whatever month i want and then be able to get an invoice amount based on that month. I figured i would need to add another column so that the invoice total will be in column D so that it will work properly. However, i can't seem to get it to work even when doing that. I've played with different formats and just at a loss right now. Any suggestions would be appreciated.

Thank you!


MonthDecember
Invoice Amount???
ChemicalInv. TotalDate
xyz8412/17/17

<tbody>
</tbody>
 
Last edited:

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows
Which one of these produces a date?

=DATEVALUE(1&B2)
=DATEVALUE(B2&1)
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows
Its a bit long because you have no date in your dropdown just a month name:

=SUMIFS(B6:B1000,C6:C1000,">="&IF(DATEVALUE(1&B2)>TODAY(),EDATE(DATEVALUE(1&B2),-12),DATEVALUE(1&B2)),C6:C1000,"<"&EDATE(IF(DATEVALUE(1&B2)>TODAY(),EDATE(DATEVALUE(1&B2),-12),DATEVALUE(1&B2)),1))

It can be shortened if you use a helper cell. You may need to swap the datevalue part around based on whats true in post number 2.
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
you could probably shorten it down a little with something like

=SUMPRODUCT(--(TEXT($C$5:$C$15,"mmmm")=B1),B5:B15) or

=SUMPRODUCT(--(MONTH(C5:C15)=MONTH(B1&1)),B5:B15)

unless I'm overlooking something
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

you could probably shorten it down a little with something like

=SUMPRODUCT(--(TEXT($C$5:$C$15,"mmmm")=B1),B5:B15) or

=SUMPRODUCT(--(MONTH(C5:C15)=MONTH(B1&1)),B5:B15)

unless I'm overlooking something

Thats one i always forget. However isnt going to work if the data is over several years so it may or may not be a problem.
 

Katich

Board Regular
Joined
Jan 22, 2008
Messages
112
how come it wouldn't work over several years? It shouldn't be a problem for what i'm working on but i'm curious.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows
Because if i took say two dates: 1st January 2017 and 1st January 2018. They both would return true if the test is just testing the month. They would both return true for January. So be careful if your data could pass over more than one year.
 

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
Exactly what Steve said but you could add the year to the month criteria, which would end up being something like...


Unknown
ABC
1MonthDecember2018
2Invoice Amount89
3
4ChemicalInv. TotalDate
5xyz8412/17/2017
6xyz8512/18/2017
7xyz8612/19/2017
8xyz8712/20/2017
9xyz8812/21/2017
10xyz8912/22/2018
11xyz9012/23/2017
12xyz9112/24/2017
13xyz9212/25/2017
14xyz9312/26/2017
15xyz9412/27/2017

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B2=SUMPRODUCT(--(TEXT($C$5:$C$15,"mmmm yyy")=B1&" "&C1),B5:B15)

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
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