agarwaldvk
02-08-2010, 01:48 PM
Hi Everybody
Can someone help me with this :-
I have data in say 3 columns 'A', 'B' and 'C' as shown - this is only a small subset of the dataset.
Col A->Col B->Col C
No->1-Sales Fulfilment->Contract Processing
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
Primary->1-Sales Fulfilment->New Connections
No->2-Billing And Device->Billing Operations
No->2-Billing And Device->Billing Operations
Secondary->2-Billing And Device->Device Operations
For each row, say in column 'D', I need to write a formula that counts the number of entries which is a concatenation of the entries in the 3 columns 'A', 'B' and 'C' without using any other other helper or intermediate columns. I do not want to use another column to contain the result of this concatenation and then use this helper column as the range criteria in the Countif() function.
For example, the count of the result of the concatenation of the 3 columns 'A', 'B' and 'C' for row 2 - which is
"No1-Sales FulfilmentCustomerTransfers"
is 13.
I can use formulas like these (shown below) for Match(), Count() and Max() :-
{=MATCH(CONCATENATE(B31,C31), (CONCATENATE(INDEX(B16:C32,,1),INDEX(B16:C32,,2))))} - Array Entered
but it doesn't seem to work with Countif().
Any assistance shall be highly valued.
Please treat this as an urgent need.
Best regards
Deepak Agarwal
Can someone help me with this :-
I have data in say 3 columns 'A', 'B' and 'C' as shown - this is only a small subset of the dataset.
Col A->Col B->Col C
No->1-Sales Fulfilment->Contract Processing
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
No->1-Sales Fulfilment->Customer Transfers
Primary->1-Sales Fulfilment->New Connections
No->2-Billing And Device->Billing Operations
No->2-Billing And Device->Billing Operations
Secondary->2-Billing And Device->Device Operations
For each row, say in column 'D', I need to write a formula that counts the number of entries which is a concatenation of the entries in the 3 columns 'A', 'B' and 'C' without using any other other helper or intermediate columns. I do not want to use another column to contain the result of this concatenation and then use this helper column as the range criteria in the Countif() function.
For example, the count of the result of the concatenation of the 3 columns 'A', 'B' and 'C' for row 2 - which is
"No1-Sales FulfilmentCustomerTransfers"
is 13.
I can use formulas like these (shown below) for Match(), Count() and Max() :-
{=MATCH(CONCATENATE(B31,C31), (CONCATENATE(INDEX(B16:C32,,1),INDEX(B16:C32,,2))))} - Array Entered
but it doesn't seem to work with Countif().
Any assistance shall be highly valued.
Please treat this as an urgent need.
Best regards
Deepak Agarwal