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?
Best Answer
-
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.
Answers
-
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.
-
This worked, thanks!
-
You're welcome @janger . I'm glad it worked for you.
Help Article Resources
Categories
Check out the Formula Handbook template!
