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!

Month	December
Invoice Amount	???
Chemical	Inv. Total	Date
xyz	84	12/17/17

<tbody>
</tbody>

 

[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

	A	B	C
1	Month	December	2018
2	Invoice Amount	89
3
4	Chemical	Inv. Total	Date
5	xyz	84	12/17/2017
6	xyz	85	12/18/2017
7	xyz	86	12/19/2017
8	xyz	87	12/20/2017
9	xyz	88	12/21/2017
10	xyz	89	12/22/2018
11	xyz	90	12/23/2017
12	xyz	91	12/24/2017
13	xyz	92	12/25/2017
14	xyz	93	12/26/2017
15	xyz	94	12/27/2017

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =SUMPRODUCT(--(TEXT($C$5:$C$15,"mmmm yyy")=B1&" "&C1),B5:B15) <tbody> </tbody>

<tbody>
</tbody>