COUNT IF WITH 2 CRITERIAS WITH SEVERALS ITEMS ON EACH CRITERIA

[Bere]

Hi, I have this problem

Problem:
I want to count in how many differents incidents each location took part of.
In each incident a location can participate many times.
I have the info like this,
INC LOC
1 A
1 A
1 B
1 C
1 B
2 B
2 D
2 D

For example, the results in this example is
A: 1. (Participate only in incident 1, it makes no different how many times
it does inside that incident, I don´t need that info )
B: 2. (incident 1 and 2)
C: 1.
D: 1.
I cannot do it with a pivot table, because when counting it will count also
the times within each incident.
Also, when using Index, it won´t count the total.

The problem with the sumproduct functions is that I have several incidents
and several location, that also aren´t fixed. They change.

 

[Bere]

Hi, I found two answers to this, but I´m not so sure if they will work every
time.

First:
(I will copy more rows first of all)
A B C D E F G
H I
1 f $B$2 $B$8 1 1 2 2 1
1 g $B$2 $B$8 2 1 3 3 1
1 f $B$2 $B$8 1 1 4 2 0
1 f $B$2 $B$8 1 1 5 2 0
1 g $B$2 $B$8 2 1 6 3 0
1 s $B$2 $B$8 6 1 7 7 1
1 e $B$2 $B$8 7 1 8 8 1
2 g $B$9 $B$14 1 8 9 9 1
2 f $B$9 $B$14 2 8 10 10 1
2 s $B$9 $B$14 3 8 11 11 1
2 s $B$9 $B$14 3 8 12 11 0
2 e $B$9 $B$14 5 8 13 13 1
2 s $B$9 $B$14 3 8 14 11 0
3 f $B$15 $B$17 1 14 15 15 1
3 s $B$15 $B$17 2 14 16 16 1
3 e $B$15 $B$17 3 14 17 17 1
4 f $B$18 $B$21 1 17 18 18 1
4 f $B$18 $B$21 1 17 19 18 0
4 f $B$18 $B$21 1 17 20 18 0
4 e $B$18 $B$21 4 17 21 21 1

The columns C and D I create them for cell reference in column E
Column C has the formula IF($A2=$A1;C1;CELL("address";$B2))
(for cell c2)
This formula says that when the number in column A change, it will return
the cell address of B2, and if not it will return the previous reference
Column D has the formula IF($A2=$A3;D3;CELL("address";$B2))
(for cell d2, then drag until 21)
This formula says that when the next number in column A change, it will
return the cell address of b2, and if not it will return the next reference
In column E I have the formula
MATCH(B2;INDIRECT($C2 & ":" &D2);0)
So it will say within the reference in column C concatenate with D, the
first position always it will be the match.
Column F has the formula
IF(A2=A1;0;COUNTIF($A:$A;$A1))+F1
So it will count how many times are of each number of column A, and also
will acumulate if the number in column A change.
Column G:
CELL("row";A2), it will display the row number.
column H: sum of column E + F
Column I:
IF(G2=H2;1;0)
THis formula will check if the cumulative count of incidents plus the match
is equal to the row number.
So for example, it will only assign a 1 if the location is the first time it
appears on the same incident number (column A). And when the incident change
it will start again.
With this column (I) I can make a pivot table to sum up the Locations.

You can try it. It works

I have another solution very similar, with 4 columns of calculation.
I will type it another day.

But, Is there an easier way?

Thanks

 

[Ashish Mathur]

Hi,

Assume that the data is in range A22:B29. In cell C22, type =B22&A22 and
copy down till C29. In cell E22, type =COUNTIF($C$22:C22,C22) and copy down
till E29. In range B31:B34, type A,B,C,D. In cell E31, type the following
formula =SUMPRODUCT(($B$22:$B$29=B31)*($E$22:$E$29=1)) and copy down till
E34.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

In other words, you want to count unique incidents for each unique location
?

Assuming the incidents are numbers as is shown in your sample...

Your data is in the range A2:B9

You have a list of the unique locations in E2:E5 = A,B,C,D

Assuming there are no empty cells in A2:A9.

Enter this array formula** in F2 and copy down to F5:

=COUNT(1/FREQUENCY(IF(B$2:B$9=E2,A$2:A$9),A$2:A$9))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.