# SUMIF formula with dates/time

#### empath89

##### New Member
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)amount offset? 6/3/2019 17:00 spoon 10 B 1.22 123 10 unmatched 6/3/2019 17:01 fork 8 S 1 123 -8 unmatched 6/3/2019 17:01 knife 12 S 1.15 123 -12 unmatched 6/13/2019 19:49 plate 15 S 2 123 -15 123/456 offset 6/13/2019 19:49 cup 30 S 2.5 123 -30 123/456 offset 6/13/2019 19:51 plate 15 B 2 456 15 unmatched 6/13/2019 19:52 cup 30 B 2.5 456 30 unmatched

<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.

#### empath89

##### New Member
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
If you format col A as general do you get values like 43619.7, or do you still see 03/06/2019 17:00?

Replies
7
Views
102
Replies
2
Views
76
Replies
2
Views
54
Replies
2
Views
68
Replies
0
Views
118

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?

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