Countifs help along with column functionality

[ace10mccloud]

I have 4 Columns: D (Containing a time), E (Containing the hours 0-23), H (Containing names), and I (Containing the list of names in H). For the first step, I am trying to discern the number of times a specific name appears per hour of the day. To do this, I wrote the function =COUNTIFS($H$H, $I$2, $D$D, $E$2:$E$25) to resolve in new column L (Count of times a name appeared per hour in a 24 hour period). This gives me the solution no problem, and I can expand the solution over the 24 hours per name. My problem occurs when I try to expand the solution across other names (when I create new columns, M, N, O, etc). My whole issues revolves around the "I" column. I can lock down the column as "I", but I want the function to follow from I2 to I3 to I4 and so forth. If I do not lock down the column "I" and expand the function to these columns, that criteria then resolves to J2, K2, J3, K3, and so forth.

[FDibbins]

I am having a hard time trying to understand what you want

can you upload a sample workbook please?

[ace10mccloud]

I can't upload right now, I apologize. I will try to recreate an example later and upload that. I would be more than happy to try and clarify as best I can through written descriptions.

[FDibbins]

Without a sample file, i will just be guessing, sorry. Maybe someone else will be able to understand your requirement better

[ace10mccloud]

Well, if you really want to be proactive, I can describe how to create a small sample file...

[ace11mccloud]

Here is a sample workbook.amcExample.xlsx

[ace10mccloud]

Did you get a chance to look at the sample file?

[XOR LX]

Hi,

Could you indicate which of the cells in this workbook contain your expected results? Or, if you haven't manually inputted them, could you do so and re-attach?

Regards

[AliGW]

I think you need to explain more clearly what the file is showing, what formula you have used and exactly where, and what you expect the results to be. By the way, I presume that we are dealing with one person here: ace10mccloud and ace11mccloud are one and the same person? If so, why have you two user IDs? It would be best to use one and stick to it.

[ace10mccloud]

Hi,

Could you indicate which of the cells in this workbook contain your expected results? Or, if you haven't manually inputted them, could you do so and re-attach?

Regards

Columns L through W should contain the expected results. Column L already contains the results from the formula use, and I am asking how to get column M (and subsequent columns) to display results relative to "I3", "I4", "I5", etc, without have to manually change the value in the function in each column.

[ace10mccloud]

I think you need to explain more clearly what the file is showing, what formula you have used and exactly where, and what you expect the results to be. By the way, I presume that we are dealing with one person here: ace10mccloud and ace11mccloud are one and the same person? If so, why have you two user IDs? It would be best to use one and stick to it.

I apologize for the 2 accounts, that it just due to the fact that I cannot upload a file from my current location. So I had to email the file to someone else to upload it for me.

I will try to clarify my problem without simply repeating myself. I am looking for results in columns L through W. The function to get the result is "=COUNTIFS($H$H, $I$2, $D$D, $E$2:$E$25)". However, I only want column L to correspond to the value in I2. I want column M to correspond to the value in I3, column N to correspond to the value in I4 and so on. I am hoping to not simply manually enter the cell into each function for each column and if there is a way around this. Does that help?

[XOR LX]

Thanks.

Since you've already entered the names again in L1:W1, simply change your formula in L2 to:

=COUNTIFS($H$2:$H$88,L$1,$D$2:$D$88,$E$2:$E$25)

Copy across and down as required.

Regards

[ace10mccloud]

Thanks.

Since you've already entered the names again in L1:W1, simply change your formula in L2 to:

=COUNTIFS($H$2:$H$88,L$1,$D$2:$D$88,$E$2:$E$25)

Copy across and down as required.

Regards

That appears to work for me! This seems to be a formatting solution though, would you happen to know if there is another solution using relative/locked cell information if I didn't have those headers?

[XOR LX]

Actually that does use relative/absolute references as a part of the solution (L$1).

Are you unable to have the column and row references in there as it stands?

Regards

[ace10mccloud]

Actually that does use relative/absolute references as a part of the solution (L$1).

Are you unable to have the column and row references in there as it stands?

Regards

It does work, but now I have a follow up question. I am trying to eliminate as much manual input as possible, and I manually transposed the column headers in row 1 from column I by simply copying and paste/special: "transpose". How would the =Transpose formula look if I wanted I2:I3 to L1:W1? I tired the function =TRANSPOSE(I2:I13) *ctrl+shift+enter* for cell L1 and got the proper result, but when I tried to expand that function across row 1 it kept the relative positions.

[XOR LX]

If you want to use an array version of TRANSPOSE, you just need to make sure that you first highlight the entire range L1:W1 before entering and committing the formula.

Alternatively, this (non-array) formula in L1 and copied across will do the same:

=INDEX($I$2:$I$13,COLUMNS($A:A))

Regards