Basic Excel Function - How to use SUMPRODUCT Function in Excel


For today's tutorial, we will be calculating the total cost of Espresso, Milk, Sugar for our café. We will be using the SUMPRODUCT Function. The SUMPRODUCT function helps us find the total cost of all our Products, Price and Quantity in 1 single step.


We will also show you how to convert your SUMPRODUCT function into an API at the end of the video.


Download: AlchemyJ FREE Community Edition: https://www.alchemyj.io/download

Excel Template: https://www.alchemyj.io/aj-excel-template

Learn More: How to setup AlchemyJ: https://www.youtube.com/watch?v=eTyYTf1sw9k&feature=youtu.be&ab_channel=AlchemyJ​​​

How AlchemyJ works: https://www.youtube.com/watch?v=bAQHo8I1xuQ&ab_channel=AlchemyJ

Let's connect: LinkedIn: https://www.linkedin.com/company/alchemyj/


Article

For today's tutorial, we will be calculating the total cost of Espresso, Milk, Sugar for our café. We will be using the SUMPRODUCT Function. The SUMPRODUCT function helps us find the total cost of all our Products, Price and Quantity in 1 single step. I hope you are as excited as I am. Without further ado, let us get started.


How to use SUMPRODUCT Function

I will be showing you our usual way of getting the total cost for our product, price and quantity. On our first step, Select Cell F6, type in equal, select on D6, key in asterisk, select on Cell E6. Hit return. You will get $2500 the total cost from Supplier A, Espresso, Price and Quantity.


In our Second Step, we will find the total cost of each supplier. Move your cursor to the lower right of cell F6. Once your cursor turns into a cross. Select it and move your cursor to cell F14 and release it. You will get the total cost of each supplier.


The Third and final step. We will find the total cost of all suppliers. Select Cell F15, type in equal, SUM, open parentheses. Move your cursor to Cell F6, select and drag your Cursor to Cell F14. Hit Return. You will get $12,850. The total cost of all supplier, product, price and quantity.


Let me show you how you can use the SUMPRODUCT Function to combine the previous 3 steps into 1 single step. Select Cell F17, type in equal, SUMPRODUCT, open parentheses. Select Cell D6 and drag your cursor to D14. Type in Comma. Select Cell E6 and drag your cursor to Cell E14. Hit Return. You will get $12,850. The same result as our previous example. Quick and easy.


Intermission

There you have it, the SUMPRODUCT 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 tutorials. 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 SUMPRODUCT function into Application Programming Interface (API)

I will be using AlchemyJ Studio Excel Add-in to turn this SUMPRODUCT 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 open API that AlchemyJ generates. I will expand this tab, and select try it out. I will input Espresso as Product, Price to be $50 and Quantity to be 10 and select Execute. We will get a SUMPRODUCT of $500. Now, let us re-do it with 2 examples. I will change the number of quantities to 100 for my 2nd example and select Execute. We will get a SUMPRODUCT of $5500, which is correct.


Closing

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.


44 views