Results 1 to 15 of 23
Thread: Counting Formula

20160711, 05:20 #1
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
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 0Last edited by dubdub; 20160711 at 05:26.
TIA
dubdub

20160711, 06:05 #2
 Join Date
 Jan 2004
 Location
 Praha
 Posts
 1,051
 Thanks
 69
 Thanked 118 Times in 103 Posts
The way your question is formatted above makes it hard to understand with any accuracy.
Can you post a sample workbook ?

20160711, 06:17 #3
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 693 Times in 629 Posts
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

20160711, 07:10 #4
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.Last edited by dubdub; 20160711 at 07:41.
TIA
dubdub

20160711, 13:10 #5
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
hi Martin,
i have included a sample workbook.TIA
dubdub

20160711, 19:06 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 693 Times in 629 Posts
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

20160711, 20:53 #7
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,731
 Thanks
 395
 Thanked 1,533 Times in 1,390 Posts
Maud,
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20160711, 21:34 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 693 Times in 629 Posts
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

20160712, 07:01 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 693 Times in 629 Posts
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

20160712, 10:51 #10
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,303
 Thanks
 3
 Thanked 206 Times in 190 Posts
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?Regards,
Rory
Microsoft MVP  Excel

20160712, 11:18 #11
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Thank you so much Maud,
i did not try the code (UDF) but the formula with helper column and COUNTIFS works prefectly.TIA
dubdub

20160712, 11:26 #12
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
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.TIA
dubdub

20160714, 01:42 #13
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 8,605
 Thanks
 56
 Thanked 1,055 Times in 983 Posts
There is a space in the post between "$F" and "$3" that has to be removed.
cheers, Paul

20160714, 03:37 #14

20160716, 11:28 #15
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
My sincer appolgy for late reply Rory,
still getting the pop window as i indicatae in my previous reply.TIA
dubdub