Search code examples
excel-formulaworksheet-function

Compare 2 set of cells and count

I'm trying to make something of a pattern recognize counting cell and calculate their probability of occurrence.

Set 1 is from a database that looks like this (consists of 6+1 columns):

ABABAB   1
CACACA   2
CACACA   2
CACACA   2
CACACA   1
ABABAB   1

Set 2 is what I input manually (only 6 columns, without the 7th column information) 

CACACA

If I want to know the probability of the 7th column as "2", pattern recognize counting cell should return 75, where if its "1" it should return 25.

Been cracking my brain using countifs function but nothing seems to work.

Solution

  • From my understanding you have seven columns, like this:

    enter image description here

    You would like to make a ratio between a combination that includes all seven columns (from A to G in the picture), and the combination of the first six columns (from A to F in the picture).

    In this case you may use COUNTIFS as follows (I will use your same example CACACA):

    =COUNTIFS(A1:A6,"C",B1:B6,"A",C1:C6,"C",D1:D6,"A",E1:E6,"C",F1:F6,"A",G1:G6,"2")/COUNTIFS(A1:A6,"C",B1:B6,"A",C1:C6,"C",D1:D6,"A",E1:E6,"C",F1:F6,"A")

    The result will be 0.75, so if you want 75 you can just multiply the result by 100.

    If you want to check the result also for CACACA 1, you just need to change the last value of the first COUNTIFS in the formula from "2" to "1" and you will get 0.25.

    In case my understanding was wrong or you need more support do not hesitate to drop me a note!