SUMIF with Different Dates

Katich

Board Regular
Joined
Jan 22, 2008
Messages
154
Office Version
  1. 2019
Platform
  1. Windows
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:
Ok, so here is my new dilemma. So now i need to see my remaining amount left which is driven by column B. I can use the same formula as you provided before but the problem is, once the delivery date is filled in, that $500 will show up in B2 and still be in B3. I would need it to drop off of the total in B3 once the date in column D was inputted.

ABCDE
MonthDecember
Inv Tot84
Remaining Amt???(should be $500)
ChemicalOrder DateInv TotalDelivery Date
xyz12/17/178412/17/17
xyz12/25/17500

<tbody>
</tbody>
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Ok, i think i will add the date in just in case i need to pull prior data. Thank you!
 
Upvote 0
theres probably a better way but maybe....

Unknown
ABCD
1MonthDecember2017
2Inv Tot84
3Remaining Amt500
4
5ChemicalOrder DateInv TotalDelivery Date
6xyz12/17/20178412/17/2017
7xyz12/25/2017500

<tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
B2=SUMPRODUCT(--(TEXT($B$6:$B$7,"mmmm yyy")=B1&" "&C1)*(D6:D7<>""),C6:C7)
B3=SUMPRODUCT(--(TEXT($B$6:$B$7,"mmmm yyy")=B1&" "&C1)*(D6:D7=""),C6:C7)

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Awesome! Thank you. Now for a stupid question. Why do you put the "--" in the formula?
 
Upvote 0
The sumproduct function cannot interpret Trues and Falses which are being returned by the conditions.

the double unary converts the trues and falses to 1's and 0's, 1's being true and 0's being false, which the sumproduct can handle

and now that you mention it, because we multiplied the conditions you don't really need the -- in this instance

=SUMPRODUCT((TEXT($B$6:$B$7,"mmmm yyy")=B1&" "&C1)*(D6:D7<>""),C6:C7)

=SUMPRODUCT((TEXT($B$6:$B$7,"mmmm yyy")=B1&" "&C1)*(D6:D7=""),C6:C7)
 
Last edited:
Upvote 0
Ok, that makes sense. Thank you for the help and explanation!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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
Back
Top