Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    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 0
    Last edited by dubdub; 2016-07-11 at 06:26.
    TIA
    dubdub

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    The way your question is formatted above makes it hard to understand with any accuracy.

    Can you post a sample workbook ?

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 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

  4. #4
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    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; 2016-07-11 at 08:41.
    TIA
    dubdub

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi Martin,
    i have included a sample workbook.
    Attached Files Attached Files
    TIA
    dubdub

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 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
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 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

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 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

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  11. #11
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    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

  12. #12
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    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

  13. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,179
    Thanks
    47
    Thanked 983 Times in 913 Posts
    There is a space in the post between "$F" and "$3" that has to be removed.

    cheers, Paul

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Quote Originally Posted by dubdub View Post
    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    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

Page 1 of 2 12 LastLast

Posting Permissions

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