Count unique values on basis of onter cell status

**azeemsarwar** · 01-13-2013, 02:16 AM

Dear All

I need your support to update the Main sheet.

Different users are adding the info in 2nd sheet (Live). I want to update the 1st sheet (main) automatically as soon as data is entered.

I need to count the unique name of countries on the basis of their status mentioned under the services type. If the service status is live for different operators existing in one country, I need to count them only once.

For row 2 in main sheet I need to count unique countries,

Example,
From B6 to B12, two different countries are mentioned.
If they are live as mentioned in H6 to H12 and J6 to J12, I need to count them uniquely and result should be 2 for B2 cell in main sheet.
If they are live as mentioned in L6 to L12 and N6 to N12, I need to count them uniquely and result should be 2 for C2 cell in main sheet.
If they are live as mentioned in P6 to P12 and R6 to R12, I need to count them uniquely and result should be 2 for D2 cell in main sheet.
If they are dates mentioned in T6 to T12 and U6 to U12, I need to count them uniquely and result should be 2 for E2 cell in main sheet.

For row 3 in main sheet I need to count only operators,

Example,
From D6 to D12, 7 different operators are mentioned.
If they are live as mentioned in H6 to H12 and J6 to J12, I need to count them uniquely and result should be 7 for B3 cell in main sheet.
If they are live as mentioned in L6 to L12 and N6 to N12, I need to count them uniquely and result should be 7 for C3 cell in main sheet.
If they are live as mentioned in P6 to P12 and R6 to R12, I need to count them uniquely and result should be 7 for D3 cell in main sheet.
If they are dates mentioned in T6 to T12 and U6 to U12, I need to count them uniquely and result should be 7 for E3 cell in main sheet.

**JBeaucaire** · 01-13-2013, 04:28 AM

Here's your first set of formulas, you edit the ranges for the next columns.

B2: =SUM(INDEX((OR(Live!$H$6:$H$12="Live",Live!$J$6:$J$12="Live"))*(MATCH(Live!$B$6:$B$12, Live!$B$6:$B$12, 0)=ROW($A$1:$A$7)),0))

B3: =SUM(INDEX((OR(Live!$H$6:$H$12="Live",Live!$J$6:$J$12="Live"))*(MATCH(Live!$D$6:$D$12, Live!$D$6:$D$12, 0)=ROW($A$1:$A$7)),0))

**azeemsarwar** · 01-13-2013, 07:05 AM

Thanks a lot for your reply, let me check with full range please...

**azeemsarwar** · 01-14-2013, 01:41 AM

Dear Jerry

Can you please check my main sheet. B2, B3 and C2, C3 are giving me the same count which is not correct even i change the range of "Live" check.

In D2, D3, i need to check the if the cell is blank or not because there is no status column available. How can i modify the same formula.

if the i need to change the OR check with AND, will it work. i tried, but it is not giving me the results.

i do the same changes in B4 to count the A column values of 2nd sheet, but it is also not working.

i am really appreciate of your kind assistance.

**JBeaucaire** · 01-14-2013, 04:44 AM

This is an ARRAY formula, you must enter it with CTRL-SHIFT-ENTER to activate the array. You will see curly braces { } appear around the formula to indicate an active array.

B2: =SUM(--(FREQUENCY(IF(((Live!H$6:H$2000="Live")+(Live!J$6:J$2000="Live")), COUNTIF(Live!$B$6:$B$2000, "<"&Live!$B$6:$B$2000), ""), COUNTIF(Live!$B$6:$B$2000, "<"&Live!$B$6:$B$2000))>0))

B3: =SUM(--(FREQUENCY(IF(((Live!H$6:H$2000="Live")+(Live!J$6:J$2000="Live")), COUNTIF(Live!$D$6:$D$2000, "<"&Live!$D$6:$D$2000), ""), COUNTIF(Live!$D$6:$D$2000, "<"&Live!$D$6:$D$2000))>0))