Menu
I have a table (a single table) like the picture below. to find the total $ Order 1, I can use SUMPRODUCT(Price,QTY1), SUMPRODUCT(Price,QTY2), however I would like to know the total $ order but devised into A,B,C. The important things I really like to keep structure of the table.
Is there any function like SUMPRODUCTIF. If not, is there anyway to make achieve what I would like to achieve?
I could make another column and do the QTY*Price to do the SUMIF. However, it adds extra columns
567
Microsoft Excel includes many functions and programs that help handle multiple spreadsheet needs, such as SUMPRODUCT IF. This function helps when comparing data and calculating large values throughout your document.
To conditionally sum or count cells with the OR logic, use the plus symbol (+) in between the arrays. In Excel SUMPRODUCT formulas, as well as in array formulas, the plus symbol acts like the OR operator that instructs Excel to return TRUE if ANY of the conditions in a given expression evaluates to TRUE.
How to use SUMPRODUCT with Multiple Criteria in Excel? We can use it in place of formulas like SUMIF. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12.
As shown in previous examples, SUMPRODUCT has the ability to add up both columns and rows. This makes it a much more versatile formula than the well-known SUMIF formula. You can add many different criteria to meet your needs. As you use the formula more often you will quickly find it your go-to formula.
=SUMPRODUCT(Price*QTY1*(Category="A"))
should do the job, with Category being the name of your "Product Category" column (that containing the letters "A", "B", "C").
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
