CODEWITHSUNDEEP
excelexcel-formulacountcontainsworksheet-function
Habib Zare
Habib Zare

Reputation: 1204

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.

Upvotes: 1

Views: 90

Answers (2)

Habib Zare
Habib Zare

Reputation: 1204

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?

Upvotes: 0

ZygD
ZygD

Reputation: 24356

Do you know how to enter Array formulas?

First formula:

=SUM((A1:A8=F$2)*(B1:B8=G$2))

Enter it as array formula (press Ctrl + Shift + Enter). After hitting the keys the formula will be embraced in curly braces { }. This formula will give you the result of 1.

Second formula:

=SUM((A1:A8=F$2)*(B1:B8<>G$2))

This is also an array formula. It gives the result of 3.

Upvotes: 2

Related Questions