How to do calculation rather than just COUNTIFS or SUMIFS

Options
✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

Are you able to provide some screenshots for reference?

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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")

• ✭✭
Options

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!

• ✭✭✭✭✭✭
Options

I'm not sure why you would need 50 helper columns?? What do you mean by "other parameters"?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!