# Excel - Find the same values in column and the do COUNTA on range in the same rows

I'd like to find all of the same names in a column, and then for each instance of that name do COUNTA on a range of cells in the same row.

I've tried using IF, but it's not accepting COUNTA.

I'm happy to reorganise the table and do it in separate steps if necessary. I can do some VBA would be fine if needed.

• Are you looking for COUNTIF function? But I'm not very clearly about your problem. Could you provide a sample?
– Lee
Mar 24 at 8:44

You are looking for an extended COUNTAIF(range, criteria, [count range]), similar to SUMIF. Unfortunately that doesn't exist so we'll have to create it!

Lets say col A has the names, and col B is the values you want to count if not blank for each grouping of names in A. I assume you have a 1000 rows including a header in row 1. Create in col C:

``````=NOT(ISBLANK(B2:B1000))+0
``````

Then in col D:

``````=SUMIF(\$A\$2:\$A\$1000, A2, \$C\$2:\$C\$1000)
``````

EDIT: All-in-one solution: If you want to do this without a helper column, try the follow array formula (type and commit with ctrl+shift+enter (CSE)).

``````=SUM(BITAND((\$A\$2:\$A\$1000=A2),NOT(ISBLANK(\$B\$2:\$B\$1000)))+0)
``````

You need to use CSE in order to tell Excel to treat the formula arguments as arrays and not just scalar single-cell numbers. Normal AND doesn't work with arrays, so BITAND is your friend. The arguments of BITAND evaluates your criteria and result in two boolean arrays which are AND'ed together row by row. The "+0" converts the resulting boolean array to a numbered array containing only 0's and 1's, which can be summed together to result in a single scalar value.

• If you have Office 365, you can use the UNIQUE function to generate a second table with all the unique names in sheet2, then use `=SUMIF(sheet1!\$A\$2:\$A\$1000, A2, sheet1!\$C\$2:\$C\$1000)` Mar 24 at 4:32

Not sure if my understanding is correct, try this formula:

=SUMPRODUCT((\$B\$2:\$E\$8<>0)*(\$A\$2:\$A\$8=A2)) 