Help build Statistic Formula

[Newbienew]

Good day,

I have to construct a statistic section for a daily used sheet. I have made a few attempts to figure this out using countif(s), sumif, and the if functions but realized that part of my issue is that my data is offset. Part of my data is in one cell, I have merged cells that I can not change, and the remaining data is in another cell that is offset from the first cell. I will explain what I am attempting to do here, but I added comments on my spreadsheet to have a better visual explanation.

I have to make stats for eight people, each having their section to feed another sheet. In my sample, A88 would be the worker's name, and the formulas would work from there. Each cell would add how many times A88 and the data appears.

What is the best formula to use, specially being I will have to copy it to other sheets as well.




Click Here for the Project


Thank you in advance for your aid.

 

[Newbienew]

Reposting for any help

 

[Newbienew]

Reposting

 

[shinigamilight]

I solved your file you can download it from here.

easyupload.io

easyupload.io

easyupload.io

 

[Newbienew]

Thank you so much. I have a question. I am not familiar with how you did the formula with just names. How did you do that?

And if it is not too much, I want to understand the formula in B/C88. Like for one what does -- do?

Thank you so much again

 

[Newbienew]

Thank you so much. I few questions.
I tried the countifs and it did not work for me. =countifs(A4:A81,"John:",E4:E81,"PH"). How come this did not work but it looks similar to your formula?

I am not familiar with how you did the formula with just names. How did you do that?

And if it is not too much, I want to understand the formula in B/C88. Like, for one what does -- do?

Thank you so much again

 

[shinigamilight]

Thank you so much. I have a question. I am not familiar with how you did the formula with just names. How did you do that?

And if it is not too much, I want to understand the formula in B/C88. Like for one what does -- do?

Thank you so much again

-- is used to turn the TRUE and FALSE into 1s and 0s respectively. In column B your start date is in every even row and your end date is in every odd so I used that logic to come up with solution. Iseven function and Isodd function is used to find the rows which are even and odd. I'd recommend to watch this video to understand the boolean logic.

 

[shinigamilight]

Thank you so much. I few questions.
I tried the countifs and it did not work for me. =countifs(A4:A81,"John:",E4:E81,"PH"). How come this did not work but it looks similar to your formula?

I am not familiar with how you did the formula with just names. How did you do that?

And if it is not too much, I want to understand the formula in B/C88. Like, for one what does -- do?

Thank you so much again

=countifs(A4:A81,"John:",E4:E81,"PH")

if you see there's a colon at the end of John that's why.

 

[Newbienew]

Sorry, there are no colons that were a typo in my response I didn't catch. =countifs(A4:A81,"John",E4:E81,"PH") When I use this, I get a 0 every time.

Thank you for much for the knowledge.

 

[shinigamilight]

Sorry, there are no colons that were a typo in my response I didn't catch. =countifs(A4:A81,"John",E4:E81,"PH") When I use this, I get a 0 every time.

Thank you for much for the knowledge.

create a new thread