Urgent - Countif as an array formula problem!

[agarwaldvk]

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

[mbarron]

Try this formula (array of course)
=SUM(IF($A$1:$A$18&$B$1:$B$18&$C$1:$C$18=A1&B1&C1,1,0)) or if you like the Concatenate function
=SUM(IF(CONCATENATE($A$1:$A$18,$B$1:$B$18,$C$1:$C$18)=CONCATENATE(A1,B1,C1) ,1,0)) or a non-array formula would be:
=SUMPRODUCT(--($A$1:$A$18=A1),--($B$1:$B$18=B1),--($C$1:$C$18=C1))

[agarwaldvk]

Dear mbarron

Mate, thank you very much. I am trying it now. I am sure it will work.

surprising I couldn't think of it myself!

Thanks again!


Best regards



Deepak Agarwal