# Help build Statistic Formula



## Newbienew (Nov 26, 2022)

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 (Nov 28, 2022)

Reposting for any help


----------



## Newbienew (Nov 29, 2022)

Reposting


----------



## shinigamilight (Nov 29, 2022)

I solved your file you can download it from here.









						easyupload.io
					

easyupload.io




					easyupload.io


----------



## Newbienew (Nov 30, 2022)

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 (Nov 30, 2022)

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 (Nov 30, 2022)

Newbienew said:


> 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 (Nov 30, 2022)

Newbienew said:


> 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?
> ...


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

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


----------



## Newbienew (Nov 30, 2022)

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 (Nov 30, 2022)

Newbienew said:


> 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


----------



## Newbienew (Nov 26, 2022)

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 (Nov 30, 2022)

I got so caught up in the formula working I didn't realize that the cell above the name is not counted. On the original version, there are no double names, I updated the link above but still left the colors that need to be calculated. 

When I applied your formula today, only the end day showed, not the start date and I realized it was counting two cells with the name "A" in it.


----------



## shinigamilight (Dec 1, 2022)

Newbienew said:


> I got so caught up in the formula working I didn't realize that the cell above the name is not counted. On the original version, there are no double names, I updated the link above but still left the colors that need to be calculated.
> 
> When I applied your formula today, only the end day showed, not the start date and I realized it was counting two cells with the name "A" in it.


I solved it again download this one
All formulas are entered while pressing ctrl shift enter 









						easyupload.io
					

easyupload.io




					easyupload.io


----------



## Newbienew (Dec 27, 2022)

@shinigamilight Good day. I was hoping you would be able to assist me with a different variation of the project. The goal I have is to have the Name, the date, and the code counted but if there are specific entries, they will not be counted. I made a small example off to the left at the top of the sheet of what the lower part should look like. I attempted to figure it out on my own in in cell B89 and below but it didn't work out to well for me. 

Project 32 retiv


----------

