Having Formula Troubles
Hello,
I am trying to count a certain number of product types within a certain work week, both referenced in other sheets. I am able to count my work week data for all product types in one column using =COUNTIFS({Calculation  Defect Rate Run Chart Range 1}, =[Work Week]@row)
I have another cell that I am trying to grab specific product types, not all. When I use this formula: =COUNTIFS({Calculation  Defect Rate Run Chart Range 2}, "Product A", {Calculation  Defect Rate Run Chart Range 1}, [Work Week]@row), I am able to only enter one product type into the Product A highlight. When I go to enter a second product type, ex. Product B, I get "0" which I know is incorrect.
I have tried doing the following: =COUNTIFS({Calculation  Defect Rate Run Chart Range 2}, "Product A" + "Product B", {Calculation  Defect Rate Run Chart Range 1}, [Work Week]@row) with no success.
Am I using the correct formula(s) for my ask? Any help will be greatly appreciated, thanks so much.
Answers

So I am not like supppeeerr clear on the question. If I got it right, you may want to use a CONTAINS function within that countifs within your logic to have it look at a range count a value based on if the description in "Product A" is found at all in the cell, that way if several things are selected, it will still be true that the cells CONTAINS the product name you are referencing.
Let me know if this does not work !
Thanks!
Nick Stafford

Thank you for the feedback. I tried using the CONTAINS function but that gives me a true or false answer, correct? When plugged into my initial equation, I get a few different error messages.
Let me try to reiterate my initial question: I created a form (Form_A) where workmanship data is to be collected. I have that information being archived into a sheet (Sheet_A) after one work week is completed (7 days). Now, I am trying to calculate lost labor hours for each work week for a subset of product types, therefore I need to count the number of variables within a column in another sheet (Sheet_B). Meaning, if column Work Week in Sheet_A says "27", count cells in column Product Line that state "Product A, Product B, Product C". I am new to SmartSheets so I am not sure if this is the best way to tackle this…
Thank you, again, for taking time to look into this.

I'm sorry dr I am still not clear, if you could include screenshots in a response I may be able to understand better visually.
That or hold tight and wait for someone who knows better than me to assist.. Sorry for the difficulty here!
Thanks!
Nick Stafford

Hi @dr123
I made some assumptions about Sheet_A and Sheet_B and created demo solutions.
Those sheets are published as editable, so you can check how the formulas work by changing the editable values. (Sheet_A: All the values are editable. Sheet_B: In the {Select Product] column, you can select the products to do the calculations.)
Sheet_A
https://app.smartsheet.com/b/publish?EQBCT=bdf46b4f29eb45d088383fcb105c23f0
Sheet_B
https://app.smartsheet.com/b/publish?EQBCT=ef01a1e9c7974f5aa757fd15b73c888c
The above Sheet_B does various calculations.
 [Product A Count] = COUNTIFS({Calculation  Defect Rate Run Chart Range 2}, "Product A", {Calculation  Defect Rate Run Chart Range 1}, [Work Week]@row)
 [Total Count] =SUM([Product A Count]@row:[Product C Count]@row)
 [A & B Count] =[Product A Count]@row + [Product B Count]@row
 [Total Lost Hours] =SUMIFS({Calculation  Defect Rate Run Chart Range 3}, {Calculation  Defect Rate Run Chart Range 1}, [Work Week]@row)
 [Average Lost Hours] =[Total Lost Hours]@row / COUNT(DISTINCT({Calculation  Defect Rate Run Chart Range 2}))
 [Select Product] You can select any product to count, sum, and average the lost hours.
 [Products Count (Selected)] =COUNTIFS({Calculation  Defect Rate Run Chart Range 2}, HAS([Select Product]@row, @cell), {Calculation  Defect Rate Run Chart Range 1}, [Work Week]@row)
 [Lost Hours (Selected)] =SUMIFS({Calculation  Defect Rate Run Chart Range 3}, {Calculation  Defect Rate Run Chart Range 2}, HAS([Select Product]@row, @cell), {Calculation  Defect Rate Run Chart Range 1}, [Work Week]@row)
 [Average Lost Hours (Selected)] =[Lost Hours (Selected)]@row / COUNTM([Select Product]@row)
Explanations
13: Calculating with COUNTIFS for each product and SUM the result is the most straightforward way.
4: You may want to calculate the lost hours.
5: COUNT(DISTINCT({Calculation  Defect Rate Run Chart Range 2})) count the number of unique product from the Sheet_A range.
7: In the COUNTIFS range & criterion, {Calculation  Defect Rate Run Chart Range 2}, HAS([Select Product]@row, @cell), I used the HAS function. The [Select Product]@row is, for example, "Product A" and "Product B". The @cell is each value of the {Calculation  Defect Rate Run Chart Range 2} range or the [Product Line] column of Sheet_A.
So the HAS function looks at the [Product Line] column in Sheet_A and, for each Product A, Product B, and so on, checks if the product line has the exact match in [Select Product]@row, for example, "Product A" and "Product B".
Help Article Resources
Categories
Check out the Formula Handbook template!