Countif + SumByColor

How did you define the color of the workers.

You do it manualy?

If you make a column to define which shift it has to be, you can use pivot table. for your problem.

Please Login or Register  to view this content.

There is no way to define that in a seperated column?

I should re-arange your data (like I showed you on sheet Output)

After that you are able to make a pivot table of the data (like I showed you on the sheet PT)

The column shift needs to be filled (manualy)

See the attached file.

you use my sheet output as your input file (or the format of my output sheet), you are able to use a pivot table.

What about the result in the pivot table.

Is this something you could work with?

Does this give you the information you need (or do you need more or less information)?

I think this is what you want. I just threw in another condition for the sum that the row has to be visible.

Well, it is not letting me edit my post so I'll just say here that I did notice the column 1 formulas were all pointing to the first shift for some reason, that is why you see all 1 instead of the numbers you should see for other shifts.

@hawkeye16

Please try also to add the changes in the text, so other forummembers also can see what the given solution is?

Can you add which cells you have changed?

He had a custom formula for SumByColor. It went through the range specified and used an if statement to check the background color after which it would add the cell to the sum. I just added another condition to the if statement to see if the row was visible so that when filtered it would only add the visible rows as he requested.

I'll try to explain better in words in the future as the code is not something people look at right away.

@hawkeye16

In which columns did you change a formula?

I looked but couldn't find it.

I expect the result 5 in the cell D78. (5 * 7)/8 = 5,25.

The result now is 1.

That is because, as mentioned above, the column 1 formulas are all pointing to the first shift option for some reason, I did not feel like correcting and reuploading. Just change the sum formula they used to look at the correct shift cell and the values are correct. I did not change any formulas or anything on the original sheet, just added some code into the custom function that was there.

@Hawkeye16

Thanks for the explaination.

Now we can wait on the reply of the OP (sordid).

Thanks for the reply.

You can add rep(utation) to the one who helped you by clicking on the star.