Counting value combos based on matching values in other columns.

[Immapotato]

I am not sure this is even possible, I am trying to find a way to count possible test combinations:

The table below is an example, I need to be able to Count how many rows have matching Name, Date, and Time with Test A and B, A and C, and A, B, and C.

Name	Test	Date	Time
AA	A	1/17/2023	22:39
AA	B	1/17/2023	22:39
AB	A	1/17/2023	7:51
AC	A	1/20/2023	21:14
AC	B	1/20/2023	21:14
AC	C	1/20/2023	21:14
AD	B	1/8/2023	19:01
AE	A	1/9/2023	9:19
AE	C	1/9/2023	23:06

The Result would look like this:

Combos	Total
A+B	1
A+C	0
A+B+C	1

OR translate this table to a format where the values of the Test column would be returned in the same row when Name, Date, and Time match 

AA	A	B		1/17/2023	22:39
AC	A	B	C	1/20/2023	21:14

Is there anyway to do this? I have thousands of entries imported into Excel and am hoping a formula can do this.

[#2]

@Immapotato 

Yes, it is possible to count possible test combinations. 

You can use a helper column and a formula based on the COUNTIFS function.

Here’s how you can do it:

1. Add a helper column to your table.
2. In the first cell of the helper column, enter the following formula and copy it down to all cells in the helper column:
3. =A2&B2&C2

This will concatenate the values in columns A, B, and C for each row.

1. In another cell, enter the following formula:
2. =COUNTIFS($D$2:$D$10,"*"&A13&"*",$D$2:$D$10,"*"&B13&"*")

Replace A13 and B13 with the values you want to count combinations for.

1. Copy the formula down to all cells in the table.

This will give you a count of how many rows have matching Name, Date, and Time with Test A and B, A and C, and A, B, and C.

I hope that helps!

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@NikolinoDE 

Thank you for your help! I am having some trouble figuring out parts of the formula you gave me (everything I know about excel is self taught using forums like these so I am probably just not using it correctly).

For =A2&B2&C2

       Am I using this in that A=the Name column, B=the Date column, and C=the Time column?

       - If yes, then this part I got to work :) though I am not sure how it works with the next         formula

For =COUNTIFS($D$2:$D$10,"*"&A13&"*",$D$2:$D$10,"*"&B13&"*")

       Is D the column the tests are in or is it the helper column? 

       - This is the part I am not getting to work as it returns "0" for everything.

If it the test column where does the helper column come in and if it is the helper column where does the test column come in?

Any clarification would be greatly appreciated!!!

[#4]

@Immapotato 

Perhaps this example can give you a better understanding of what I mean.

If it is not what you want, or it is what you want, just give us feedback, please.

[#5]

@NikolinoDE 

Not quite what I need, though I think my example was unclear so below is more like what I am talking about. Only the ones with Name, Date, and Time matching need to be counted to find how many have both Test A and B, or Test A and C, or All three. I did find a round about way to do it using COUNTIF. It is not pretty but I think it is working. Thank you for your help!

Name	Test	Date	Time
Test, Potato	A	1/17/2023	22:39
Test, Potato	B	1/17/2023	22:39
Fake, Name	A	1/17/2023	7:51
Llama, Iona	A	1/20/2023	21:14
Llama, Iona	B	1/20/2023	21:14
Llama, Iona	C	1/20/2023	21:14
Mashed, Potato	B	8/1/2023	19:01
Bioled, Potato	A	9/1/2023	9:19
Boiled, Potato	C	9/1/2023	23:06
Combos	Total
AB	1
AC	0
ABC	1