Best way to count a value across a numerous columns

[AndyGW]

I have a sheet with a list of names split into 8 columns. Inbetween each of these is a further 3 columns containing other pieces of numerical data. I am currently counting how many times a certain name occurs in all the columns by using COUNTIF(F1,AJ100,"name"). This works but it is counting across the entire field, eg 32 columns. I could do COUNTIF(F1,F100,"name")+COUNTIF(K1,K100)+ etc etc, but is there a more elegant way?

[jason.b75]

Maybe sumproduct.

=SUMPRODUCT(--(A1:Z99="name"))

Adjust the range to suit.

Or just use =COUNTIF(A1:Z99,"name")

Unless the columns that you're excluding might contain the name that you want to count, it will make no difference having them in the formula.

edit:-

I misread your question, why are you looking for a different way? If it works why try to fix something that isn't broken?

[AndyGW]

Yes it does work I was just thinking of the calculation overhead. That range will grow and grow, and I am counting more and more names in the field. Counting how many times a name appears in a few columns, across a field of 4 x 4 cells is one thing but what about when I am counting 20+ names across a field which could be 100 cells wide by a 1000 deep?

It is unnecessary for the code to count 75% of the field, because the names only appear in one out of four columns, but that's what the code is currently doing.

[samba_ravi]

if you upload a sample excel file with expected result you will get the better result

[AndyGW]

I guess what Im asking for is a shorthand way of writing COUNTIF(F1,F100,"name")+COUNTIF(K1,K100)+COUNTIF(O1,O100)etc etc, something like COUNTIF(F1,F100,K1,K100,O1,O100,"name")... eg count single columns across a wide date field without actually having to type in up to 20 COUNTIF statements or count multiple columns that dont need counting Is there a way of doing this?

[oeldere]

Please Login or Register  to view this content.

maybe there are other (better) solutions.

[jason.b75]

It is unnecessary for the code to count 75% of the field, because the names only appear in one out of four columns, but that's what the code is currently doing.

Unnecessary, but more efficient, even with 100 columns by 1000 rows, which to put in perspective, is less than 10% of a whole column, yet you would probably use =COUNTIF(A:A,"name") without a second thought.

20+ names to count is negligible for a range that size, viewing this thread is probably using more processing effort than those countif formulae will waste.

[Tony Valko]

As long as "name" doesn't appear in the columns between the target columns then:

=COUNTIF(F1:AJ100,"name")

Is the best option.