Pulls & Multiplying Multiple Data Points Based on Year Criteria-CodePudding

Having an issue figuring out a proper Google sheets formula that works

=SUM(IF(AND('Online Arbitrage'!A:A = D5, 'Online Arbitrage'!M:M >=DATE(A2,1,1), 'Online Arbitrage'!M:M <=DATE(A2,12,31), 'Online Arbitrage'!N:N> 0),'Online Arbitrage'!H:H*'Online Arbitrage'!N:N))

I tried this formula in the "Taxes" worksheet and other variants with no success.

I am needing it to pull through the total purchases per supplier from the "Online Arbitrage" sheet based on the year that I have entered into A2.

In this example, I am expecting a result of $2,696.04 and it is producing a result of $0. I've tried other variations of this formula and it produced a result of around $5,700 which was also incorrect.

CodePudding user response：

Try:

=sumproduct(('Online Arbitrage'!$A$4:$A=D5)*('Online Arbitrage'!$M$4:$M>=DATE(A2,1,1))*('Online Arbitrage'!$M$4:$M<=DATE(A2,12,31))*('Online Arbitrage'!$N$4:$N>0)*'Online Arbitrage'!$H$4:$H*'Online Arbitrage'!$N$4:$N)

You generally can't use AND/OR statements in arrays as they aggregate so won't give you the expected result. Instead, use * instead of AND and instead of OR. I've also limited your ranges to avoid the header cells.