How to do calculation rather than just COUNTIFS or SUMIFS

Danmu Wu

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

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

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

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

Are you able to provide some screenshots for reference?

Danmu Wu

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

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

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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.