For this tutorial, I have created an Excel Sheet of Wallmart's Cost of Inventory Replenishment. These Fast-moving consumer goods include Starbucks, TWG, Coke, Evian, Pepsi and Nescafe. We will also show you how to convert your SUMIFS function in an API at the end of the video. Stay Tune.
Download: AlchemyJ FREE Community Edition: https://www.alchemyj.io/download
Learn More: How to setup AlchemyJ: https://www.youtube.com/watch?
How AlchemyJ works: https://www.youtube.com/watch?v=bAQHo8I1xuQ&ab_channel=AlchemyJ
Let's connect: LinkedIn: https://www.linkedin.com/company/alchemyj/
How to use the SUMIFS Function in Excel? For this tutorial, I have created an Excel Sheet of Wallmart's Cost of Inventory Replenishment. These Fast-moving consumer goods include Starbucks, TWG, Coke, Evian, Pepsi and Nescafe. If you wish to learn the SUMIF function. We had made a video on it. Do check out the link above.Without further ado, let us get started.
How to use SUMIFS Function
Select on Cell C19 and type in the equal, SUMIFS, open parentheses. We will now select the sum range. Select Cell E6 and drag your cursor to E17 and type in a comma. Now, we will select our area criteria. Select Cell C6 and drag your cursor to C17 and type in a comma. Next, select on our interactive Cell C20 and type in a comma. We will select our Date of Purchase criteria. Select Cell D6 and drag your cursor to D17 and type in a comma. We want to find the total sum of purchase after 1st April 2021. Next, insert quotes, larger than, equal and close the quotes. Type in ampersand and select our interactive Cell C21. Close the parentheses and hit on the return key. You will get $81,435 the total sum of Date of purchase of 23 April 2021 and 18 October 2021.
3 Condition Example
Let us try to add the 3rd condition. We are interested in finding out the total sum of purchase for the North area after 1st April and cost below $50,000. We will use the previous example and add in our 3rd criteria. Select Cell C19 and double click on it. At the end of the formula, type in a comma. Select Cell E6 and drag your cursor to E17 and type in a comma. Next, insert quotes, smaller than, equal and close the quotes. Type in ampersand and select our interactive Cell C22. Close the parentheses and hit on the return key. You will get $81,435. The same result as our previous example. Let us try $40,000 and see if our value changes. We got a total of $39,303. The only sum on our data chart that is below $40,000.
There you have it, the SUMIFS function for Excel. If you have any excel formula you want us to teach, let us know in the comment section below. Don't forget to subscribe, hit the bell button and turn on the notification to have more free excel function tutorial. But, before you go, we have a bonus clip for you. I will be turning this Excel spreadsheet into an API. Stay tuned to see how the magic works.
Convert SUMIFS function into Application Programming Interface (API)
I will be using AlchemyJ Studio Excel Add-in to turn this SUMIFS Function Excel into an API. To do this, I will need AlchemyJ Excel installed and with a pre-set of rules. I will select the AlchemyJ ribbon, select Generate API and click on Generate API. The file is now converting. Okay, your API is ready.
I will now launch this openAPI which AlchemyJ generates, and try using the SUMIFS Function on it. I will expand this tab, and select try it out. Let’s try an example of $30,000. What will we have? Zero. It is because all of our cost is above $30,000. Okay, let us use our previous example of North area after 1st of April and cost below $40,000. Will we be getting back the same amount of $39,303? Awesome, we got it.
AlchemyJ Studio Excel Add-in turns your Excel business model into API. This way, you can integrate your Excel model into other applications. You can download AlchemyJ Studio Excel Add-in for free at alchemyj.io. And once again, thank you for watching. So which Excel function you wish to be converted into an API? Let us know in the comment section below. Lastly, don't forget to subscribe, hit the bell button and turn on the notification. See you soon.