Having Formula Troubles

dr123

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.

NickStaffordPM

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 !

dr123

@NickStaffordPM,

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.

NickStaffordPM

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!

jmyzk_cloudsmart_jp

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.

1. [Product A Count] = COUNTIFS({Calculation - Defect Rate Run Chart Range 2}, "Product A", {Calculation - Defect Rate Run Chart Range 1}, [Work Week]@row)
2. [Total Count] =SUM([Product A Count]@row:[Product C Count]@row)
3. [A & B Count] =[Product A Count]@row + [Product B Count]@row
4. [Total Lost Hours] =SUMIFS({Calculation - Defect Rate Run Chart Range 3}, {Calculation - Defect Rate Run Chart Range 1}, [Work Week]@row)
5. [Average Lost Hours] =[Total Lost Hours]@row / COUNT(DISTINCT({Calculation - Defect Rate Run Chart Range 2}))
6. [Select Product] You can select any product to count, sum, and average the lost hours.
7. [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)
8. [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)
9. [Average Lost Hours (Selected)] =[Lost Hours (Selected)]@row / COUNTM([Select Product]@row)

Explanations

1-3: 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".