How to do calculation rather than just COUNTIFS or SUMIFS

Danmu Wu · 09/09/21

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

Paul Newcome · 09/09/21

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

Danmu Wu · 09/09/21

https://community.smartsheet.com/discussion/comment/304376#Comment_304376

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

Paul Newcome · 09/09/21

Are you able to provide some screenshots for reference?

Danmu Wu · 09/09/21

https://community.smartsheet.com/discussion/comment/304409#Comment_304409

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.

Paul Newcome · 09/09/21

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

Danmu Wu · 09/09/21

https://community.smartsheet.com/discussion/comment/304417#Comment_304417

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!

Paul Newcome · 09/09/21

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

How to do calculation rather than just COUNTIFS or SUMIFS

Answers

Help Article Resources

Categories