SUMIF formula with dates/time

E

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:

[TABLE="width: 799"]
<tbody>[TR]
[TD="class: m_-5595038335429118977gmail-xl268, width: 135, align: right"](A)date/time[/TD]
[TD="class: m_-5595038335429118977gmail-xl269, width: 96, align: right"](B)product[/TD]
[TD="class: m_-5595038335429118977gmail-xl270, width: 82, align: right"](C)amount[/TD]
[TD="class: m_-5595038335429118977gmail-xl269, width: 90, align: right"](D)Buy/Sell[/TD]
[TD="class: m_-5595038335429118977gmail-xl269, width: 76, align: right"](E)price[/TD]
[TD="class: m_-5595038335429118977gmail-xl269, width: 85, align: right"](F)account[/TD]
[TD="class: m_-5595038335429118977gmail-xl260, width: 85, align: center"](G)amount[/TD]
[TD="class: m_-5595038335429118977gmail-xl272, width: 150, align: right"]offset?[/TD]
[/TR]
[TR]
[TD="class: m_-5595038335429118977gmail-xl265, align: right"]6/3/2019 17:00[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]spoon[/TD]
[TD="class: m_-5595038335429118977gmail-xl266, align: right"]10[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]B[/TD]
[TD="align: right"]1.22[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-5595038335429118977gmail-xl264, align: right"]10[/TD]
[TD="class: m_-5595038335429118977gmail-xl267, align: right"]unmatched[/TD]
[/TR]
[TR]
[TD="class: m_-5595038335429118977gmail-xl265, align: right"]6/3/2019 17:01[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]fork[/TD]
[TD="class: m_-5595038335429118977gmail-xl266, align: right"]8[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]S[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-5595038335429118977gmail-xl264, align: right"]-8[/TD]
[TD="class: m_-5595038335429118977gmail-xl267, align: right"]unmatched[/TD]
[/TR]
[TR]
[TD="class: m_-5595038335429118977gmail-xl265, align: right"]6/3/2019 17:01[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]knife[/TD]
[TD="class: m_-5595038335429118977gmail-xl266, align: right"]12[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]S[/TD]
[TD="align: right"]1.15[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-5595038335429118977gmail-xl264, align: right"]-12[/TD]
[TD="class: m_-5595038335429118977gmail-xl267, align: right"]unmatched[/TD]
[/TR]
[TR]
[TD="class: m_-5595038335429118977gmail-xl265, align: right"]6/13/2019 19:49[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]plate[/TD]
[TD="class: m_-5595038335429118977gmail-xl266, align: right"]15[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]S[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-5595038335429118977gmail-xl264, align: right"]-15[/TD]
[TD="class: m_-5595038335429118977gmail-xl267, align: right"]123/456 offset[/TD]
[/TR]
[TR]
[TD="class: m_-5595038335429118977gmail-xl265, align: right"]6/13/2019 19:49[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]cup[/TD]
[TD="class: m_-5595038335429118977gmail-xl266, align: right"]30[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]S[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]123[/TD]
[TD="class: m_-5595038335429118977gmail-xl264, align: right"]-30[/TD]
[TD="class: m_-5595038335429118977gmail-xl267, align: right"]123/456 offset[/TD]
[/TR]
[TR]
[TD="class: m_-5595038335429118977gmail-xl265, align: right"]6/13/2019 19:51[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]plate[/TD]
[TD="class: m_-5595038335429118977gmail-xl266, align: right"]15[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]B[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]456[/TD]
[TD="class: m_-5595038335429118977gmail-xl264, align: right"]15[/TD]
[TD="class: m_-5595038335429118977gmail-xl267, align: right"]unmatched[/TD]
[/TR]
[TR]
[TD="class: m_-5595038335429118977gmail-xl265, align: right"]6/13/2019 19:52[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]cup[/TD]
[TD="class: m_-5595038335429118977gmail-xl266, align: right"]30[/TD]
[TD="class: m_-5595038335429118977gmail-xl271, align: right"]B[/TD]
[TD="align: right"]2.5[/TD]
[TD="align: right"]456[/TD]
[TD="class: m_-5595038335429118977gmail-xl264, align: right"]30[/TD]
[TD="class: m_-5595038335429118977gmail-xl267, align: right"]unmatched[/TD]
[/TR]
</tbody>[/TABLE]

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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

S
  • Solved
SumIf problem
Replies
2
Views
350
sharshra
S
C
  • Solved
SUMIFS with EOM Date
Replies
5
Views
418
ChetanPuri
C
G
  • Solved
Split items into Rows
Replies
2
Views
524
GreyFox8991
G
A
  • Solved
Count entry by time
Replies
3
Views
194
admvlad
A
8
  • Solved
How to avoid spill error on Sumif
Replies
4
Views
344
Eric W
Eric W

Forum statistics

Threads
1,223,888
Messages
6,175,206
Members
452,618
Latest member
Tam84

Share this page

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