SUMIF formula with dates/time

[empath89]

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

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	(A)date/time	(B)product	(C)amount	(D)Buy/Sell	(E)price	(F)account	(G)amount	offset?
2	03/06/2019 17:00	spoon	10	B	1.22	123	10	unmatched
3	03/06/2019 17:01	fork	8	S	1	123	-8	unmatched
4	03/06/2019 17:01	knife	12	S	1.15	123	-12	unmatched
5	13/06/2019 19:49	plate	15	S	2	123	-15	123/456 offset
6	13/06/2019 19:49	cup	30	S	2.5	123	-30	unmatched
7	13/06/2019 19:51	plate	15	B	2	456	15	unmatched
8	14/06/2019 19:52	cup	30	B	2.5	456	30	unmatched
Sheet1

Cell Formulas
Range	Formula
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")

 

[empath89]

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]

If you format col A as general do you get values like 43619.7, or do you still see 03/06/2019 17:00?