How to do calculation rather than just COUNTIFS or SUMIFS
Hi,
I have a table like a inventory with all goods with unit price. Such as:
A1 A 100 $5.00
B1 B 200 $10.00
A2 A 150 $7.00
B2 B 50 $12.00
C1 C 80 $20.00
If I want to count how many "A" I have now, I can use COUNTIF(A).
My challenge is, I need to calculate the total price of all goods under category A, like:
=SUMIF(amount*price, category, ="A").
I know SUMIF or SUMIFS can only add up all numbers match the condition, does Smartsheet support my scenario where I want to do calculation rather than simply adding up all number?
Thanks,
Danmu
Answers

You would do your SUMIFS to get the total number of "A" and then multiply by the price.
=SUMIFS(Amount:Amount, Part:Part, "A") * 5

Paul, thanks for the reply. Let me clarify. So I have A1 and A2 under category A, while A1 and A2 have different number of goods and different unit price, so my intent is to get:
A1*5+A2*7, not (A1+A2)*5

Are you able to provide some screenshots for reference?

My table is not as simple as this one, as I have hundreds of lines and it's updated dynamically. I want to do the calculation in one cell, to get the total price for all Apple products in the inventory.

Honestly, the easiest way to accomplish this would be to add in a helper column and get your total price on each row.
=Quantity@row * [Unit Price]@row
Then use a SUMIFS on the Helper column
=SUMIFS([Helper Column]:[Helper Column], Category:Category, @cell = "Apple")

Hi Paul, Helper column is a good idea, at least can achieve it. Any other "complicated" way or formula can do that same thing without helper column? In my table there are around 50 parameters so I need 50 helper columns which is kind of another burden :)
But thanks a lot for the solution!

I'm not sure why you would need 50 helper columns?? What do you mean by "other parameters"?
Help Article Resources
Categories
Check out the Formula Handbook template!