SUMIF formula with dates/time

empath89

New Member
Joined
Oct 30, 2018
Messages
6
Hi,

So I was hoping someone could help me out with formulas. i have some data and I would like a formula that can show me if data offsets between two accounts. So I have the below data:

(A)date/time(B)product(C)amount(D)Buy/Sell(E)price(F)account(G)amountoffset?
6/3/2019 17:00spoon10B1.2212310unmatched
6/3/2019 17:01fork8S1123-8unmatched
6/3/2019 17:01knife12S1.15123-12unmatched
6/13/2019 19:49plate15S2123-15123/456 offset
6/13/2019 19:49cup30S2.5123-30123/456 offset
6/13/2019 19:51plate15B245615unmatched
6/13/2019 19:52cup30B2.545630unmatched

<tbody>
</tbody>

So, right now, I have formulas in the amount column and the offset column.
amount formula: IF(D2="B",C2,-C2)

offset? formula: IF(AND(F2=123,SUMIFS(<wbr>G:G,F:F,123,E:E,E2)+SUMIFS(G:<wbr>G,F:F,456,E:E,E2)=0),"123/456 offset","unmatched")

So if the account is 123, I want it to take the sum of the amount column (G) taking the account, product, and price into account and see if it has be offset by the sum of the same details in 456 account. I would also like to be able to take the date into account (ignoring the time) - so that if there was a cup sold at 2.5 on 6/14, it would be able to differentiate that and have separate sums it would be looking to offset for the 6/13 and the 6/14 cups.

(As an aside, I would also love it if I could somehow incorporate the amount directly into the offset formula and delete the amount formula, but it's not the one I really need to work.)
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">(A)date/time</td><td style="text-align: right;;">(B)product</td><td style="text-align: right;;">(C)amount</td><td style="text-align: right;;">(D)Buy/Sell</td><td style="text-align: right;;">(E)price</td><td style="text-align: right;;">(F)account</td><td style="text-align: center;;">(G)amount</td><td style="text-align: right;;">offset?</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">03/06/2019 17:00</td><td style="text-align: right;;">spoon</td><td style="text-align: right;;">10</td><td style="text-align: right;;">B</td><td style="text-align: right;;">1.22</td><td style="text-align: right;;">123</td><td style="text-align: right;;">10</td><td style=";">unmatched</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">03/06/2019 17:01</td><td style="text-align: right;;">fork</td><td style="text-align: right;;">8</td><td style="text-align: right;;">S</td><td style="text-align: right;;">1</td><td style="text-align: right;;">123</td><td style="text-align: right;;">-8</td><td style=";">unmatched</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">03/06/2019 17:01</td><td style="text-align: right;;">knife</td><td style="text-align: right;;">12</td><td style="text-align: right;;">S</td><td style="text-align: right;;">1.15</td><td style="text-align: right;;">123</td><td style="text-align: right;;">-12</td><td style=";">unmatched</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">13/06/2019 19:49</td><td style="text-align: right;;">plate</td><td style="text-align: right;;">15</td><td style="text-align: right;;">S</td><td style="text-align: right;;">2</td><td style="text-align: right;;">123</td><td style="text-align: right;;">-15</td><td style=";">123/456 offset</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">13/06/2019 19:49</td><td style="text-align: right;;">cup</td><td style="text-align: right;;">30</td><td style="text-align: right;;">S</td><td style="text-align: right;;">2.5</td><td style="text-align: right;;">123</td><td style="text-align: right;;">-30</td><td style=";">unmatched</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">13/06/2019 19:51</td><td style="text-align: right;;">plate</td><td style="text-align: right;;">15</td><td style="text-align: right;;">B</td><td style="text-align: right;;">2</td><td style="text-align: right;;">456</td><td style="text-align: right;;">15</td><td style=";">unmatched</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">14/06/2019 19:52</td><td style="text-align: right;;">cup</td><td style="text-align: right;;">30</td><td style="text-align: right;;">B</td><td style="text-align: right;;">2.5</td><td style="text-align: right;;">456</td><td style="text-align: right;;">30</td><td style=";">unmatched</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">F2=123,SUMPRODUCT(<font color="Green">(<font color="Purple">INT(<font color="Teal">$A$2:$A$8</font>)=INT(<font color="Teal">A2</font>)</font>)*(<font color="Purple">$B$2:$B$8=B2</font>)*(<font color="Purple">$D$2:$D$8<>D2</font>)*(<font color="Purple">$E$2:$E$8=E2</font>)</font>)>0</font>),"123/456 offset","unmatched"</font>)</td></tr></tbody></table></td></tr></table><br />
 

empath89

New Member
Joined
Oct 30, 2018
Messages
6
So sorry for such a late reply! Unfortunately it's giving me the #VALUE ! error message - seems it's the INT part that's giving that error message. Will play around with it but if you have any more ideas (or came across this issue), any advice would be helpful.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,444
Office Version
  1. 365
Platform
  1. Windows
If you format col A as general do you get values like 43619.7, or do you still see 03/06/2019 17:00?
 

Forum statistics

Threads
1,141,413
Messages
5,706,301
Members
421,441
Latest member
VapesRub

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