EXcel COUNT values based on multiple conditions in different ranges

Question

I have an Excel spreadsheet, contain all possible combinations between certain products, e.g. a-a, a-b, a-c, b-a, b-b, b-c, c-a, c-b and c-c. Based on those combinations, a value is calculated and compared with a historical value. The two values are subtracted, resulting in -1, 0 or 1.

Now, in another spreadsheet, I have all the products listed (so that would be a, b and c here) and for each product, I would like to know how many -1's, 0's and 1's the product had as a result when it was the second product in the combination, e.g. I want to know how many ...-b's resulted in 0.

My first thought was to use a simple COUNTIF, going over the range with the subtraction: COUNTIF(RANGE:0). Of course, this gives all the 0's in the range, without taking into account the product. Then, I tried SUM(IF(AND("range of the second product"="b";"range of the subtraction result"=0);1)), but this produces #N/A. I am unsure what to try out next.

In some other related topics, the suggestion was made to make use of arrays, based on http://www.cpearson.com/excel/ArrayFormulas.aspx

Consequently, I tried the formula {=COUNT(("2ndproductrange"="b") * ("resultrange"<0))}, but this returned the total number of rows. A variant with {=COUNTIF(("2ndproductrange"="b")*("resultrange");<0)} isn't a valid formula.

Answer 1

It sounds like to me that you are trying to perform a COUNT operation that matches 2 distinct criteria. As you noted the COUNTIF formula takes in a single criteria, well there is a COUNTIFS formula that takes in multiple. Here is what I "think" it would look like with your example ranges:

=COUNTIFS(2ndproductrange;"b";resultrange;"<0")

A concrete example would be as follows:

       A               B       C       D        E   F   G
---------------------------------------------------------
Historical Value    Product        Countifs     a   b   c
       1               c              <0        1   2   0
      -1               a               0        0   1   1
      -1               b              >0        1   2   1
       1               b                    
       1               b                    
       0               c                    
      -1               b                    
       0               b                    
       1               a

In the above example the formulas would be:

=COUNTIFS($B:$B;"a";$A:$A;"<0") =COUNTIFS($B:$B;"b";$A:$A;"<0") =COUNTIFS($B:$B;"c";$A:$A;"<0")
=COUNTIFS($B:$B;"a";$A:$A;"0")  =COUNTIFS($B:$B;"b";$A:$A;"0")  =COUNTIFS($B:$B;"c";$A:$A;"0")
=COUNTIFS($B:$B;"a";$A:$A;">0") =COUNTIFS($B:$B;"b";$A:$A;">0") =COUNTIFS($B:$B;"c";$A:$A;">0")

For those that are using a comma , as their list separator locale the same formulas would be:

=COUNTIFS($B:$B,"a",$A:$A,"<0") =COUNTIFS($B:$B,"b",$A:$A,"<0") =COUNTIFS($B:$B,"c",$A:$A,"<0")
=COUNTIFS($B:$B,"a",$A:$A,"0")  =COUNTIFS($B:$B,"b",$A:$A,"0")  =COUNTIFS($B:$B,"c",$A:$A,"0")
=COUNTIFS($B:$B,"a",$A:$A,">0") =COUNTIFS($B:$B,"b",$A:$A,">0") =COUNTIFS($B:$B,"c",$A:$A,">0")