SUMIF with Different Dates

[Katich]

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!


[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]December[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Invoice Amount[/TD]
[TD]???[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chemical[/TD]
[TD]Inv. Total[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]xyz[/TD]
[TD]84[/TD]
[TD]12/17/17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Which one of these produces a date?

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

 

[steve the fish]

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]

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

 

[Katich]

Which one of these produces a date?

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

Sorry, i'm not sure i know what you're asking.

 

[Katich]

This worked great! Thank you!!

 

[steve the fish]

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]

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]

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]

Exactly what Steve said but you could add the year to the month criteria, which would end up being something like...


Unknown[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Month[/TD]
[TD]December[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Invoice Amount[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Chemical[/TD]
[TD]Inv. Total[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]xyz[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]12/17/2017[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]xyz[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]12/18/2017[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]xyz[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]12/19/2017[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]xyz[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]12/20/2017[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]xyz[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]12/21/2017[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]xyz[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]12/22/2018[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]xyz[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]12/23/2017[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]xyz[/TD]
[TD="align: right"]91[/TD]
[TD="align: right"]12/24/2017[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]xyz[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"]12/25/2017[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]xyz[/TD]
[TD="align: right"]93[/TD]
[TD="align: right"]12/26/2017[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]xyz[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]12/27/2017[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B2[/TH]
[TD="align: left"]=SUMPRODUCT(--(TEXT($C$5:$C$15,"mmmm yyy")=B1&" "&C1),B5:B15)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]