Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF WITH 2 CRITERIAS WITH SEVERALS ITEMS ON EACH CRITERIA
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF WITH 2 CRITERIAS WITH SEVERALS ITEMS ON EACH CRITERIA
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 "Bere" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF WITH 2 CRITERIAS WITH SEVERALS ITEMS ON EACH CRITERIA
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 "Bere" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNT IF WITH 2 CRITERIAS WITH SEVERALS ITEMS ON EACH CRITERIA
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. -- Biff Microsoft Excel MVP "Bere" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Filtered Visible Items that Match Numeric Criteria between two ranges | Excel Worksheet Functions | |||
Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
How can I count items in multiple columns with different criteria. | Excel Worksheet Functions | |||
Count of items using multiple criteria | Excel Worksheet Functions | |||
Find Count of Items with certain criteria | Excel Discussion (Misc queries) |