Count Number of Cells in Column B if Cells in Column A are less?

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows
Hi Guys

Driving me mad this o_O

Lets say we have a list of numbers in Columns BD and Column BE as below and I want to count how many of the cells in Column BE are greater then the adjacent Cells in Column BD? I have tried Countif and Countifs (=COUNTIF(BE:BE,">"&BD2)) but it counts anything in the column which is above that particular cell i.e cell BE2 or BE3 and so on. I can do a simple =IF(BE2>BD2,1,"") in one Column to return 1's then =COUNT(BJ:BJ) to count the 1's but its just so long winded and I want to be able to use it in VBA with Application.WorksheetFunction.CountIf(). I was really hoping Countif would have worked but I think I have a mental block due to working on it for so long!

1605279771993.png


Thanks in advance and have a great weekend!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.
 

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows
Sorry, Office 2007. I hadn't realised they had added that. It looks like there's been a lot of changes since I was last on here. Been registered since 2002!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
There have indeed been a lot of changes. :)
Thanks for updating your profile.
Try
Excel Formula:
=SUMPRODUCT(--(BE2:BE100>BD2:BD100))
 

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

That's Great and works fine but I am have never used that formula before and cant get my head around what its doing even after looking at the help files :confused::confused:

What's the dashes for -- and are the inner brackets to signify an array?

I am just writing the VBA Version of this and will post it on here one I have it working.

Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
The -- is a double unary & it converts an array of True/False into 1 or 0 & the sumproduct then adds them up.
 

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Now I'm struggling with the VBA side of it :confused: I have wrote it as below but I get a Type Mismatch Error! Any Ideas? LstRw is declared earlier in the code and represents the last used row.

VBA Code:
    Debug.Print Application.WorksheetFunction.SumProduct _
    (--(Sheets("Orders").Range("BE2:BE" & LstRw) > Sheets("Orders").Range("BD2:BD" & LstRw)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
One way to get the count in VBA is
VBA Code:
Sub razzandy()
   Dim Ary As Variant
   Dim RngBE As Range
   
   With Sheets("Orders")
      Set RngBE = .Range("BE2", .Range("BE" & Rows.Count).End(xlUp))
      Ary = Filter(.Evaluate("transpose(if(" & RngBE.Address & ">" & RngBE.Offset(, -1).Address & ",1,""x""))"), "x", False)
   End With
   MsgBox UBound(Ary) + 1
End Sub
 
Solution

razzandy

Active Member
Joined
Jun 26, 2002
Messages
388
Office Version
  1. 2007
Platform
  1. Windows
One way to get the count in VBA is
VBA Code:
Sub razzandy()
   Dim Ary As Variant
   Dim RngBE As Range
  
   With Sheets("Orders")
      Set RngBE = .Range("BE2", .Range("BE" & Rows.Count).End(xlUp))
      Ary = Filter(.Evaluate("transpose(if(" & RngBE.Address & ">" & RngBE.Offset(, -1).Address & ",1,""x""))"), "x", False)
   End With
   MsgBox UBound(Ary) + 1
End Sub

That's great Fluff Thankyou! I changed it slightly as below but its all derived from you:

VBA Code:
   With Sheets("Orders")
        Set RngBE = .Range("BE2:BE" & LstRw)
        Ary = Filter(.Evaluate("transpose(if(" & RngBE.Address & ">" & RngBE.Offset(, -1).Address & ",1,""x""))"), "x", False)
   End With
        Debug.Print UBound(Ary) + 1

I am still wondering how I could get the Application.WorksheetFunction.SumProduct working. I'm just inquisitive and it bugs me when I can't suss something! I could probably get the arrays in but not sure about the -- bit, where it converts into 0 and 1's. Maybe I need to post this as a new question what do you think?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,737
Office Version
  1. 365
Platform
  1. Windows
I rarely use Worksheetfunction in VBA, so not sure why code doesn't work.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,014
Messages
5,628,142
Members
416,294
Latest member
McStuffins

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top