+ Reply to Thread
Results 1 to 4 of 4

count values from one column based on condition, with array

  1. #1
    Registered User
    Join Date
    03-31-2014
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    48

    count values from one column based on condition, with array

    Hi,

    I have several values in Column "I" and in Column "H" 2 values = "Check", "OK". Now I can Count the number of "Targets" for each Target manually, but cannot code with an Array which could do it automatically.check.PNG
    I wanted to have 2-D Array, which would Show me following for "Check". Is there any "quick" way to do it with Array? (of course I could do it with Pivot, but just wanted with Array). Could you help further?

    Target_1 Count 3
    Target_4 Count 1

    HTML Code: 
    Sub Czek()
    Dim Zelle As Range
    Dim Counter, Counter1 As Integer
    Counter = 0
    For Each Zelle In Range("I9:I16")
    If Zelle.Value = "Target_1" And Zelle.Offset(0, -1) = "Check" Then
        Counter1 = Zelle.Count
        Counter = Counter + Counter1
    End If
    Next Zelle
    Debug.Print "Target_1" & " "; Counter
    End Sub

  2. #2
    Forum Expert p24leclerc's Avatar
    Join Date
    07-05-2010
    Location
    Québec
    MS-Off Ver
    Excel 2003-2010-2013
    Posts
    2,078

    Re: count values from one column based on condition, with array

    Why don't you want to use a formula with the COUNTIFS function? You can use multiple criteria.
    Pierre Leclerc
    _______________________________________________________

    If you like the help you got,
    Click on the STAR "Add reputation" icon at the bottom.

  3. #3
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: count values from one column based on condition, with array

    As mentioned you could use COUNTIFS

    Something like:

    Please Login or Register  to view this content.
    If you will have more unique target values then you could first loop the target range and create an array or collection of the uniques instead of my const/split method. This gave me the counts 3,0,0,1,0. If you need to actually place the counts somewhere you would need to assign the result of the function to a variable or range objects value.

  4. #4
    Registered User
    Join Date
    03-31-2014
    Location
    Germany
    MS-Off Ver
    Excel 2003
    Posts
    48

    Re: count values from one column based on condition, with array

    thank you, I thought of collections or dictionaries, but I am not so far...will try with your method

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find MIN & MAX values based on an IF condition for an array.
    By Quasar82 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-25-2017, 12:51 PM
  2. count unique values based on 1 condition
    By penfolda in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-05-2014, 10:28 PM
  3. Required Unique values based on if condition (array)
    By Punnam in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-30-2014, 01:09 AM
  4. Replies: 4
    Last Post: 07-16-2013, 09:45 AM
  5. [SOLVED] Sum Large values along with Count Condition - Array Formula
    By acsishere in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-28-2013, 05:45 PM
  6. give a count of values based on a condition in different column
    By ashdrago in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-27-2012, 11:13 AM
  7. Count unique values based on condition
    By pradeev in forum Excel General
    Replies: 4
    Last Post: 06-12-2012, 10:43 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1