Search code examples
excelexcel-formulacountcontainsworksheet-function

Count cells containing a value and that value is in other range

I have a list like this:

enter image description here

I want to count the names (column B) that have value in column A like a or b or ... also this pair value is in range (F,G columns) .

How to do it?

For example:
count of b mark values is 4, but I am trying to count b mark except (b reza) , because the b reza is in the range F,G;

Count of b is 3.
Count of b (special b) is 1.

Solution

  • I do below things after trying more.

    enter image description here

    in the H column i use this formula :

    =COUNTIFS($A:$A;F1;$B:$B;G1)>0

    in row 2 : 

    =COUNTIFS($A:$A;F2;$B:$B;G2)>0

    and copy this formula in all; This formula give me (if the result is TRUE) that current row of G,H List is in A,B list. Count of the TRUE values are the spacial values that i call them الف . I can count all special data in A,B list (20):

    =COUNTIF(A:A;"a")+COUNTIF(A:A;"b")+COUNTIF(A:A;"c")

    The count of special values that not in F,G list is 20-6=14 i call them ب.

    this is what i want. but perhaps this is not good method. any idea?