I'm trying to do a subtotal count.

Can I get a count of the number of red shirts, yellow shirts, green shirts, red pants, green pants, etc.? I know I can use countif item=shirt, and color=red, etc. But my actual sheet I'm working with has a very long list of items so writing a formula for each item would be too tedious. Is there a way to do a subtotal by item? Or write the formula so it calculates the subtotal every time the item changes?

Find more posts tagged with

Accepted answers

Summer

My recommendation is to create a Key that will allow you to easily replicate your ask across all item types such as shirt, pants, socks.

In my example it's all done on one sheet, but your counts can be done on a secondary sheet, you'd just have to update the reference.

=COUNTIFS(Item:Item, $[Key Item]@row, Color:Color, $[Key Colors]$1)

Counts all instances where the Key Item is found in the Item Column AND where the color red (as indicated by $[Key Colors]$1 is found in the Color column.

The $[Key Colors]$1 reference was updated to be $2, $3, $4 depending on the color I was looking for. The "$" makes it an absolute reference and allowed me to put my formulas into the first column and then drag them down to the pants, socks, and blank rows. Eliminating the need to create the formula for each variation.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

All comments

Summer

My recommendation is to create a Key that will allow you to easily replicate your ask across all item types such as shirt, pants, socks.

In my example it's all done on one sheet, but your counts can be done on a secondary sheet, you'd just have to update the reference.

=COUNTIFS(Item:Item, $[Key Item]@row, Color:Color, $[Key Colors]$1)

Counts all instances where the Key Item is found in the Item Column AND where the color red (as indicated by $[Key Colors]$1 is found in the Color column.

https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

janger

This worked, thanks!

Summer

You're welcome @janger . I'm glad it worked for you.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.