Counting instances of cells containing numbers greater than zero in the row below a specified value

michaeltsmith93 · Nov 17, 2018

Hello,

As I described in the title, I'd like help with a formula that will allow me to count how many times the cell below a specified value is a number greater than zero in another sheet within a specified range. So let's say that I choose "J" as my specified value. I'd like the formula to find every instance of "J" within a range on another sheet and count the instances when the value in the cell directly below each J is a number greater than zero. I have tried messing with AND in the criteria in COUNTIF, but I can't figure out a way to ensure that the relative reference is looking for "J." Similar problems with INDEX MATCH.

I'd also appreciate some recommendations on resources for learning VBA. I know that that's probably the easiest way to accomplish something like this.

Thanks so much in advance.

Michael

Aladin Akyurek · Nov 17, 2018

Control+shift+enter, not just enter:

=SUM(IF($A$2:$A$8="J",IF($A$3:$A$9>0,1)))

Is this what you are after? Otherwise, a sample would be helpful.

michaeltsmith93 · Nov 18, 2018

Ah, my mistake. I'd thought about that solution, but it doesn't work without me having to enter every row in which I want to search. Let me show you.[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]J[/TD]
[TD]I[/TD]
[TD]n[/TD]
[TD]J[/TD]
[TD]X[/TD]
[TD]b[/TD]
[TD]r[/TD]
[TD]E[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]n[/TD]
[TD]J[/TD]
[TD]X[/TD]
[TD]b[/TD]
[TD]r[/TD]
[TD]E[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]I[/TD]
[TD]n[/TD]
[TD]J[/TD]
[TD]X[/TD]
[TD]b[/TD]
[TD]r[/TD]
[TD]E[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]I[/TD]
[TD]n[/TD]
[TD]d[/TD]
[TD]X[/TD]
[TD]b[/TD]
[TD]r[/TD]
[TD]E[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD]I[/TD]
[TD]n[/TD]
[TD]J[/TD]
[TD]X[/TD]
[TD]b[/TD]
[TD]r[/TD]
[TD]E[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD]n[/TD]
[TD]d[/TD]
[TD]X[/TD]
[TD]b[/TD]
[TD]r[/TD]
[TD]E[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I'd really like is for all of these situations to be covered:
1. The 3 in the Row 3 is counted.
2. The 2 in the Row 3 is counted.
3. The 3 in Row 7 is counted (because it's associated with the J two rows above).
4. The 5 in row 11 is not counted (because it's associated with the X, not the J, from the original "target").

Item number 4 would work by combining it with an existing VBA script that I have for Levenshtein distance, if you're familiar with that.

I'm sure that the best way to do this would be to write a script, but I'm a total VBA novice. Ideas? Is there somewhere that I could pay someone to write this?

Akuini · Nov 18, 2018

3. The 3 in Row 7 is counted (because it's associated with the J two rows above).
4. The 5 in row 11 is not counted (because it's associated with the X, not the J, from the original "target").

I don’t understand the criteria:
Why the 3 in Row 7 is counted? Isn’t it associated with the ‘d’ above it.
Is it because ‘d’ is lower case or what?

KolGuyXcel · Nov 18, 2018

Try with a helper column - Column J!

I've assumed the data to be from 1st Row in a set of 4 rows with the 4th row as blank, always.

Formula in helper column J:
Enter as array formula (CTRL_SHIFT+ENTER)
=IF($A1="","",IF(MOD(ROW()-1,4)=0,SUM(IF((--($A1:$I1="J"))+(--($A2:$I2="J"))-(--($A2:$I2="X"))>0,IF($A3:$I3>0,1))),0))
and drag it down to the last row of data!

Then sum Column J!

KolGuyXcel · Nov 18, 2018

For a VBA code, you can try
The data is again assumed to be in block of 4 rows with the 4th row to be blank starting from the 1st row and in columns from A to I.

Code:

Sub count_val()
    Dim lr As Long, val As Long, ir As Long, ic As Long
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    val = 0
    For ir = 1 To lr Step 4
        For ic = 1 To 9
            If Cells(ir + 1, ic).Value2 <> "X" And Cells(ir, ic).Value2 = "J" Or Cells(ir + 1, ic).Value2 = "J" Then
                If Cells(ir + 2, ic).Value2 > 0 Then
                    val = val + 1
                End If
            End If
        Next
    Next
    MsgBox "Total : " & val
End Sub

Rick Rothstein · Nov 18, 2018

Assuming only data appears on your sheets and that you will call this function from within other VBA code, simply pass in the text you want to find ("J" in this case) and the following function will return the count you asked for.

Code:

[table="width: 500"]
[tr]
	[td]Function FindCount(TextToFind As String) As Long
  Dim R As Long, C As Long, LastRow As Long, LastCol As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  LastCol = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious, , , False).Column
  For R = 1 To LastRow Step 4
    For C = 1 To LastCol
      If Cells(R, C) = "J" Or Cells(R + 1, C) = "J" Then If Cells(R + 2, C) Then FindCount = FindCount + 1
    Next
  Next
End Function[/td]
[/tr]
[/table]

Counting instances of cells containing numbers greater than zero in the row below a specified value

michaeltsmith93

Board Regular

Excel Facts

Aladin Akyurek

MrExcel MVP

michaeltsmith93

Board Regular

Akuini

Well-known Member

KolGuyXcel

Board Regular

KolGuyXcel

Board Regular

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Counting instances of cells containing numbers greater than zero in the row below a specified value

michaeltsmith93

Board Regular

Excel Facts

Aladin Akyurek

MrExcel MVP

michaeltsmith93

Board Regular

Akuini

Well-known Member

KolGuyXcel

Board Regular

KolGuyXcel

Board Regular

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock