Count number of non-empty columns in a multi-row range

[amandanewt]

Hello,

I need a formula to count the number of columns that contain text, within a multi row range.

So in the attached example:
- For each row, insert a formula in Column N that calculates:
- Looking at all other rows that have the same ID as this row in Column M
- Count the number of columns between Column A and Column L inclusive that are not empty

So in the first row of the example, which has the ID 'CN_011' in Column M, there are 5 rows in total that also have that value. Looking at these 5 rows, there are 5 instances of columns (between Column C and Column N) that are not empty. So the answer I need is '5'.

Hoping someone out there can help!

Many thanks in advance
Amanda

[ragulduy]

I think:
=SUM(IF(FREQUENCY(IF(M$2:M$11=M2,1,0)*IF(A$2:L$11<>"",COLUMN(A1:L1),0),COLUMN(A1:L1))<>0,1,0))+1
in N2 as an array formula (confirm with ctrl+shift+enter) and then copied down as neccessary would work.

[amandanewt]

Hey ragulduy, thanks for your reply!

I copied and pasted your formula in but it seems to be giving me the wrong answer - it's giving me a result of 7 where it should be 5, and 3 where it should be 1. In fact, putting the formula into my main spreadsheet with the full dataset, the value given seems to be consistently over by 2. Shall I just put a '- 2' into the formula? Or have you any ideas for fixing the formula?

thanks lots
Amanda

[amandanewt]

Sorry I just realised that all I needed to do is change the +1 at the very end of your formula to -1, and it works perfectly!

So the formula is:
=SUM(IF(FREQUENCY(IF(M$2:M$11=M2,1,0)*IF(A$2:L$11<>"",COLUMN(A1:L1),0),COLUMN(A1:L1))<>0,1,0))-1

Thanks so much ragulduy!!