Hi,

This can be done in a Power Pivot Pivot Table.  Have you got PP? What version of Excel do you have?

See attached for solution, and this post to describe the process

Excel PivotTable Distinct Count

I've also rearranged the data into a table in proper tabular format.

Regards

Phil

Dear Philip;

Thank for your quick reply. I added a file which I am exactly working on it. I tried the study but couldn't reach the correct welder numbers. Actually on "Workshop Progress" sheet we have welder column (AB) but regarding that I added two columns (AN,AO). I am not sure without that columns we can get the correct number of welders by the way. My problem is that I want to see on the same pivot (Welder&Dia sheet), welding date (column AC), Inch (column R) and welder numbers (Column AB or column AN/AO). Can you please help me for that? 

Note: Please remind that some welders are duplicated one the same day which we shouldn't count. If it is possible to don't show the blank cell information.

Many thanks...

Hi Gokhan,

No file is attached to your reply.

Did you try my file?

Regards

Phil

Ciao Phil; sorry; the file is attached. I tried but something wrong on my formulas I guess.

Hi Gokhan,

In Phil's example file he has split welder 1 and 2 onto separate rows, not separate columns as you have done. You need to format your data in a tabular format like Phil has in his file.

Then you can use PivotTables to summarise the data and get your counts, like Phil's example. Note: you haven't said how the Inches should be split between welders 1 and 2. e.g. row 31 has 24 inches and 2 welders. Do they get 12 inches each or 24 or something else?

Also, if a welder appears multiple times on the same date, which inches do you count for them?

Mynda

Ciao Mynda;

Good to hear you. My first problem to see how many welders we have per day. Inch I can handle in another basic pivot. No problem. The table is welding database so more less at construction sites; table should be like that. I can't change it but if you prefer to do it easily like Phil format; I am glad to learn it also.

Thanks...

Hi Gokhan,

You can use Power Query to fix the layout of the data into a tabular format enabling you to count the number of welders per day. See Sheet1 of the attached file where I've fixed the layout of the Workshop Progress data and then used a PivotTable to count the number of welders per welding date.

I hope that points you in the right direction. If you'd like to learn how to use Power Query, please consider my Power Query course.

Mynda