CODEWITHSUNDEEP
excelexcel-formula
Orkhan Orkhan
Orkhan Orkhan

Reputation: 105

Conditional if and sum

I have a table which represents the brands and stock for last 10 months. WHat I need is:

Identify the SKU which have been sold less than 10 (quantity) for last 10 months.

The problem is that sometimes the december stock is 6 but January stock is 31, and if I subtract that I get -25. And this alters the formula and I am unable to calculate the products which have been sold less than 10 times for last 10 months.

I use the formula like below : 

=IF((D2-E2)+(E2-F2)+(F2-G2)+(G2-H2)+(H2-I2)+(I2-J2)+(J2-K2)+(K2-L2)+(L2-M2)<10;IF((D2-E2)+(E2-F2)+(F2-G2)+(G2-H2)+(H2-I2)+(I2-J2)+(J2-K2)+(K2-L2)+(L2-M2)>0;"Overstock";"No Overstock"))

Thanks in advance !

enter image description here

Upvotes: 1

Views: 252

Answers (2)

Justyna MK
Justyna MK

Reputation: 3563

I think you should be able to use SUMPRODUCT in order to achieve your results:

=IF(E2>D2,SUMPRODUCT(E2:L2-F2:M2),SUMPRODUCT(D2:L2-E2:M2))

Afterwards, you can simply use =IF(AND(N2>0,N2<10),"Overstock","No Overstock") to mark the amount as Overstock or No Overstock.

Let me know if it works for you.

enter image description here

Upvotes: 1

Mutahid Shah
Mutahid Shah

Reputation: 1

Kindly use SumIfs() formula for this issue

Upvotes: 0

Related Questions