Real Life Problem:
In a departmental store, the Store Manager needs to buy Onion in January month for getting discount from the Dealer. The Manager get the vegetable buying target in December. He applied a SUMPRODUCT() to get the total kilograms of Onion he must buy to get the discount from the Dealer in January. But the problem is, In Kilograms column, somehow text data entered. And that is why the SUMPRODUCT() is not working. It shows a #VALUE error. Now the question is, how can you help the Store Manager to get the correct total kilograms of Onion need to buy in January by ignoring the #VALUE Error?
In a departmental store, the Store Manager needs to buy Onion in January month for getting discount from the Dealer. The Manager get the vegetable buying target in December. He applied a SUMPRODUCT() to get the total kilograms of Onion he must buy to get the discount from the Dealer in January. But the problem is, In Kilograms column, somehow text data entered. And that is why the SUMPRODUCT() is not working. It shows a #VALUE error. Now the question is, how can you help the Store Manager to get the correct total kilograms of Onion need to buy in January by ignoring the #VALUE Error?
Example Data Table:
Below is the target for the Store Manager month wise got in December:
Solution:
In this situation There are 2 main things you can suggest to the Store Manager for getting the correct result. First one is by removing the text value from the Kilograms column. and you will get the result 321.
The next one is handling the error. Means Getting the correct result by ignoring the errors by using the same formula. But before doing this, lets see what formula the Store Manager used:
The Store Manager used below formula:
Below is the target for the Store Manager month wise got in December:
Image 1: Target data
Solution:
In this situation There are 2 main things you can suggest to the Store Manager for getting the correct result. First one is by removing the text value from the Kilograms column. and you will get the result 321.
The next one is handling the error. Means Getting the correct result by ignoring the errors by using the same formula. But before doing this, lets see what formula the Store Manager used:
Image 2: Error Value in SUMPRODUCT()
The Store Manager used below formula:
=SUMPRODUCT((B3:B11="Onion")*(D3:D11="Jan")*(C3:C11<>"x")*(C3:C11))
The formula is absolutely correct, but the problem is SUMPRODUCT() can return only values. If any TEXT value added in same range then it will not calculate and returned #VALUE Error.
To help the Store Manager you can apply an Array Formula like below:
{=SUMPRODUCT((B3:B11="Onion")*(D3:D11="Jan")*(C3:C11<>"x"))*(C3:C11)}
Image 3: Got the result
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw
|||| Please SUBSCRIBE our YouTube Channel ||||
https://www.youtube.com/channel/UCIWaA5KCwZzBGwtmGIOFjQw