Counting the sum of valid entries

[poor_cookie]

I would like to count the sum of all the ones from A11 to A17. They are obtained by applying a formula on (A2:B8) and discussed in #post-5605524

So, from A11 to A17, the sum will be 3. However, the list of IDs is dynamic (there could be 10 IDs, or 100 IDs). I think the SUM formula can help, but instead of displaying 100 rows of zeroes and ones before summing up, is there a way that I can find the sum of all the ones, regardless of the size of the data, on a single cell?

 

[Fluff]

How about

=SUM(--(FREQUENCY(IF(B2:B8=D2,A2:A8),A2:A8)>0))

This is an array formula & needs to be confirmed with Ctrl Shift Enter & not just Enter.

 

[poor_cookie]

Hello Fluff, thanks for the prompt response. I have tried with your solution, and it worked.

If I changed my ID into a series of alphanumeric characters, the array formula no longer works (the output from the formula is 0 in cell D4) . Could you show us how we can process alphanumeric data as well, or a new thread is required?

 

[Fluff]

Have a look here Excel formula: Count unique text values with criteria | Exceljet