# Thread: Counting Formula

1. ## Counting Formula

greetings,

I want do the counting under each column and consider only cell value(s) that solely appear in that column only. Something similar to the below Metrics.

A B C D E
AAA 1
BBBB 1
CCC 2 1
DDD1 1
RRR3 1

COUNT 2 1 1 1 0

2. The way your question is formatted above makes it hard to understand with any accuracy.

Can you post a sample workbook ?

3. dubdub,

It's unclear how your matrix is setup. But if it is how I suspect then this can be achieved with helper columns that indicate the duplicated values in each column.

Next to the matrix, in G1 enter the formula =COUNTIF(B1:B\$5,B1) then copy down to G5

In G6, enter the formula to count the 1's (original value) =COUNTIF(G1:G5,1). Anything > 1 in column G is a duplicate.

Lastly, copy G1:G6 across to column J

DUBDUB1.png

HTH,
Maud

4. hi Maudibe
building on your image and considering that B:E represent headings, then staring from left to right D&E should show a zero count since for D there are values exist across the rows ddd & eee in B and for E there is a value in row ccc in column C. I am not sure if I was able to clarify it.

5. hi Martin,
i have included a sample workbook.

6. dubdub,

Thanks for the explanation. Would have never figured that is what you wanted to do. This can be don with a helper column G. In G3, place the formula =COUNTA(B3:F3) then copy down to G7. This will count non blank values across the rows.

In C9, place the following formula =SUMPRODUCT(C3:C7 * (\$G\$3:\$G\$7=1)) then copy across to F9. This will count the non blank values in each column if it is the only value across the row looking for a 1 in column G.

dubdub2.png

HTH,
Maud

7. Maud,

8. Thanks RG but due to the nature of Sumproduct this only works if the values are only ones (as shown in dubdub's example). Perhaps, Countif(s) would work better if values can be other than a 1. Will wait for the OP's reply.

Maud

9. An alternative to Sumproduct using a UDF that will use any value including a 1

As above, a helper column G. In G3, place the formula =COUNTA(B3:F3) then copy down to G7. This will count non blank values across the rows.

In a standard module, place the following code
Code:
```Public Function COUNTCOL(rng As Range, col As String) As Integer
Dim cell As Range
For Each cell In rng
If cell <> "" And Cells(cell.Row, col) = 1 Then
COUNTCOL = COUNTCOL + 1
End If
Next cell
End Function```

Then in C9, add the following formula and copy across to F9:
=COUNTCOL(C3:C7,"G")

HTH,
Maud

10. To do it without a helper column:

=SUMPRODUCT((C3:C7<>"")*(SUBTOTAL(3,OFFSET(\$B\$3:\$F \$3,ROW(\$B\$3:\$B\$7)-MIN(ROW(\$B\$3:\$B\$7)),0))=1))

One question though: if there is no value in col A, but a value in col B and col D, should it count as 1 in col B and 0 in col D, or 0 in both?

11. Thank you so much Maud,

i did not try the code (UDF) but the formula with helper column and COUNTIFS works prefectly.

12. Hi Rory,
i tried the formula but i keep getting an excel pop window with highlight of the 3,offset part of the formula.
As for your question, it should count 1 in B and 0 in C.

13. There is a space in the post between "\$F" and "\$3" that has to be removed.

cheers, Paul

14. Originally Posted by dubdub
As for your question, it should count 1 in B and 0 in C.
In that case I think you need:
Code:
`=SUMPRODUCT((C3:C7<>"")*(SUBTOTAL(3,OFFSET(\$B\$3,ROW(\$B\$3:\$B\$7)-MIN(ROW(\$B\$3:\$B\$7)),0,1,COLUMNS(\$B2:C2)))=1))`
I don't think any of the prior formulas will calculate like that.

15. My sincer appolgy for late reply Rory,

still getting the pop window as i indicatae in my previous reply.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•