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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
How about


Excel 2013/2016
ABCDEFGH
1(A)date/time(B)product(C)amount(D)Buy/Sell(E)price(F)account(G)amountoffset?
203/06/2019 17:00spoon10B1.2212310unmatched
303/06/2019 17:01fork8S1123-8unmatched
403/06/2019 17:01knife12S1.15123-12unmatched
513/06/2019 19:49plate15S2123-15123/456 offset
613/06/2019 19:49cup30S2.5123-30unmatched
713/06/2019 19:51plate15B245615unmatched
814/06/2019 19:52cup30B2.545630unmatched
Sheet1
Cell Formulas
RangeFormula
H2=IF(AND(F2=123,SUMPRODUCT((INT($A$2:$A$8)=INT(A2))*($B$2:$B$8=B2)*($D$2:$D$8<>D2)*($E$2:$E$8=E2))>0),"123/456 offset","unmatched")
 
Upvote 0
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.
 
Upvote 0
If you format col A as general do you get values like 43619.7, or do you still see 03/06/2019 17:00?
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

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