Seeking a better way to CountIF with dynamic range without dragging cells down columns.

P

Poker Joe

New Member
Joined
Feb 12, 2015
Messages
30
Office Version
  1. 2019
Platform
  1. Windows
The preview sheet is a small sample of a much larger worksheet but it gives an idea of what I'm trying to figure out.


Is there a better way to CountIf without typing in the CountIf Function in the top row of cells, adjusting all the cell addresses, then selecting the top row of formulas and dragging them down the sheet?

Essentially, I have the CountIf Range values (Integers) starting in C10:G LastRow or cells (10,3), cells (LastRow, 7).

The first two columns are an ID number and Date. They're not part of the CountIf Range.

The criterias of the CountIf function are in I9:AY9. Each cell is a criteria. Criteria values are Integers. They are static $I$9, $J$9, $K$9, etc.

The Formulas start in I53 through AY53. I want to CountIf the values of the -43 rows, Range, with criteria values in I9 through AY9.

The -43 rows would be dynamic as I drag the selected cells down the spreadsheet.

Additionally, I'd like to go two rows past the LastRow of range data. This would give me an idea of criteria results before filling in the data of the LastRow.

Thanks in advance to anyone that can crack this. ChatGPT gave up. I explained it a thousand ways. No help or I can't vibe with AI. That's fine.

If you prefer to do something simple using the preview, I can probably edit it with the cell addresses, row count, etc. I need for the larger worksheet. I had to zoom down too small to use it as a preview showing formulas.

Book2.xlsx
ABCDEFGHIJKLMNOPQR
8Larger worksheet has Integers (criteria) from 1 to 43, I9:AY9
9ID1DateC1C2C3C4C512345678910
1014492736958
11244928589105
1234492912422Formulas shows last -5 rows,
13444930456610 I really need last -43 rows for
14544931610277larger worksheet.
1564493282438=COUNTIF($C10:$G14,$I$9)=COUNTIF($C10:$G14,$J$9)=COUNTIF($C10:$G14,$K$9)=COUNTIF($C10:$G14,$L$9)=COUNTIF($C10:$G14,$M$9)=COUNTIF($C10:$G14,$N$9)=COUNTIF($C10:$G14,$O$9)=COUNTIF($C10:$G14,$P$9)=COUNTIF($C10:$G14,$Q$9)=COUNTIF($C10:$G14,$R$9)
1674493356952=COUNTIF($C11:$G15,$I$9)=COUNTIF($C11:$G15,$J$9)=COUNTIF($C11:$G15,$K$9)=COUNTIF($C11:$G15,$L$9)=COUNTIF($C11:$G15,$M$9)=COUNTIF($C11:$G15,$N$9)=COUNTIF($C11:$G15,$O$9)=COUNTIF($C11:$G15,$P$9)=COUNTIF($C11:$G15,$Q$9)=COUNTIF($C11:$G15,$R$9)
1784493427661=COUNTIF($C12:$G16,$I$9)=COUNTIF($C12:$G16,$J$9)=COUNTIF($C12:$G16,$K$9)=COUNTIF($C12:$G16,$L$9)=COUNTIF($C12:$G16,$M$9)=COUNTIF($C12:$G16,$N$9)=COUNTIF($C12:$G16,$O$9)=COUNTIF($C12:$G16,$P$9)=COUNTIF($C12:$G16,$Q$9)=COUNTIF($C12:$G16,$R$9)
18944935103474=COUNTIF($C13:$G17,$I$9)=COUNTIF($C13:$G17,$J$9)=COUNTIF($C13:$G17,$K$9)=COUNTIF($C13:$G17,$L$9)=COUNTIF($C13:$G17,$M$9)=COUNTIF($C13:$G17,$N$9)=COUNTIF($C13:$G17,$O$9)=COUNTIF($C13:$G17,$P$9)=COUNTIF($C13:$G17,$Q$9)=COUNTIF($C13:$G17,$R$9)
19104493675186=COUNTIF($C14:$G18,$I$9)=COUNTIF($C14:$G18,$J$9)=COUNTIF($C14:$G18,$K$9)=COUNTIF($C14:$G18,$L$9)=COUNTIF($C14:$G18,$M$9)=COUNTIF($C14:$G18,$N$9)=COUNTIF($C14:$G18,$O$9)=COUNTIF($C14:$G18,$P$9)=COUNTIF($C14:$G18,$Q$9)=COUNTIF($C14:$G18,$R$9)
20114493786417=COUNTIF($C15:$G19,$I$9)=COUNTIF($C15:$G19,$J$9)=COUNTIF($C15:$G19,$K$9)=COUNTIF($C15:$G19,$L$9)=COUNTIF($C15:$G19,$M$9)=COUNTIF($C15:$G19,$N$9)=COUNTIF($C15:$G19,$O$9)=COUNTIF($C15:$G19,$P$9)=COUNTIF($C15:$G19,$Q$9)=COUNTIF($C15:$G19,$R$9)
2112449382710110=COUNTIF($C16:$G20,$I$9)=COUNTIF($C16:$G20,$J$9)=COUNTIF($C16:$G20,$K$9)=COUNTIF($C16:$G20,$L$9)=COUNTIF($C16:$G20,$M$9)=COUNTIF($C16:$G20,$N$9)=COUNTIF($C16:$G20,$O$9)=COUNTIF($C16:$G20,$P$9)=COUNTIF($C16:$G20,$Q$9)=COUNTIF($C16:$G20,$R$9)
22134493918943=COUNTIF($C17:$G21,$I$9)=COUNTIF($C17:$G21,$J$9)=COUNTIF($C17:$G21,$K$9)=COUNTIF($C17:$G21,$L$9)=COUNTIF($C17:$G21,$M$9)=COUNTIF($C17:$G21,$N$9)=COUNTIF($C17:$G21,$O$9)=COUNTIF($C17:$G21,$P$9)=COUNTIF($C17:$G21,$Q$9)=COUNTIF($C17:$G21,$R$9)
23144494041366=COUNTIF($C18:$G22,$I$9)=COUNTIF($C18:$G22,$J$9)=COUNTIF($C18:$G22,$K$9)=COUNTIF($C18:$G22,$L$9)=COUNTIF($C18:$G22,$M$9)=COUNTIF($C18:$G22,$N$9)=COUNTIF($C18:$G22,$O$9)=COUNTIF($C18:$G22,$P$9)=COUNTIF($C18:$G22,$Q$9)=COUNTIF($C18:$G22,$R$9)
24154494169574=COUNTIF($C19:$G23,$I$9)=COUNTIF($C19:$G23,$J$9)=COUNTIF($C19:$G23,$K$9)=COUNTIF($C19:$G23,$L$9)=COUNTIF($C19:$G23,$M$9)=COUNTIF($C19:$G23,$N$9)=COUNTIF($C19:$G23,$O$9)=COUNTIF($C19:$G23,$P$9)=COUNTIF($C19:$G23,$Q$9)=COUNTIF($C19:$G23,$R$9)
251644942754101=COUNTIF($C20:$G24,$I$9)=COUNTIF($C20:$G24,$J$9)=COUNTIF($C20:$G24,$K$9)=COUNTIF($C20:$G24,$L$9)=COUNTIF($C20:$G24,$M$9)=COUNTIF($C20:$G24,$N$9)=COUNTIF($C20:$G24,$O$9)=COUNTIF($C20:$G24,$P$9)=COUNTIF($C20:$G24,$Q$9)=COUNTIF($C20:$G24,$R$9)
261744943106734=COUNTIF($C21:$G25,$I$9)=COUNTIF($C21:$G25,$J$9)=COUNTIF($C21:$G25,$K$9)=COUNTIF($C21:$G25,$L$9)=COUNTIF($C21:$G25,$M$9)=COUNTIF($C21:$G25,$N$9)=COUNTIF($C21:$G25,$O$9)=COUNTIF($C21:$G25,$P$9)=COUNTIF($C21:$G25,$Q$9)=COUNTIF($C21:$G25,$R$9)
271844944339910=COUNTIF($C22:$G26,$I$9)=COUNTIF($C22:$G26,$J$9)=COUNTIF($C22:$G26,$K$9)=COUNTIF($C22:$G26,$L$9)=COUNTIF($C22:$G26,$M$9)=COUNTIF($C22:$G26,$N$9)=COUNTIF($C22:$G26,$O$9)=COUNTIF($C22:$G26,$P$9)=COUNTIF($C22:$G26,$Q$9)=COUNTIF($C22:$G26,$R$9)
28194494557559=COUNTIF($C23:$G27,$I$9)=COUNTIF($C23:$G27,$J$9)=COUNTIF($C23:$G27,$K$9)=COUNTIF($C23:$G27,$L$9)=COUNTIF($C23:$G27,$M$9)=COUNTIF($C23:$G27,$N$9)=COUNTIF($C23:$G27,$O$9)=COUNTIF($C23:$G27,$P$9)=COUNTIF($C23:$G27,$Q$9)=COUNTIF($C23:$G27,$R$9)
29204494668223=COUNTIF($C24:$G28,$I$9)=COUNTIF($C24:$G28,$J$9)=COUNTIF($C24:$G28,$K$9)=COUNTIF($C24:$G28,$L$9)=COUNTIF($C24:$G28,$M$9)=COUNTIF($C24:$G28,$N$9)=COUNTIF($C24:$G28,$O$9)=COUNTIF($C24:$G28,$P$9)=COUNTIF($C24:$G28,$Q$9)=COUNTIF($C24:$G28,$R$9)
30214494784485=COUNTIF($C25:$G29,$I$9)=COUNTIF($C25:$G29,$J$9)=COUNTIF($C25:$G29,$K$9)=COUNTIF($C25:$G29,$L$9)=COUNTIF($C25:$G29,$M$9)=COUNTIF($C25:$G29,$N$9)=COUNTIF($C25:$G29,$O$9)=COUNTIF($C25:$G29,$P$9)=COUNTIF($C25:$G29,$Q$9)=COUNTIF($C25:$G29,$R$9)
312244948=COUNTIF($C26:$G30,$I$9)=COUNTIF($C26:$G30,$J$9)=COUNTIF($C26:$G30,$K$9)=COUNTIF($C26:$G30,$L$9)=COUNTIF($C26:$G30,$M$9)=COUNTIF($C26:$G30,$N$9)=COUNTIF($C26:$G30,$O$9)=COUNTIF($C26:$G30,$P$9)=COUNTIF($C26:$G30,$Q$9)=COUNTIF($C26:$G30,$R$9)
322344949=COUNTIF($C27:$G31,$I$9)=COUNTIF($C27:$G31,$J$9)=COUNTIF($C27:$G31,$K$9)=COUNTIF($C27:$G31,$L$9)=COUNTIF($C27:$G31,$M$9)=COUNTIF($C27:$G31,$N$9)=COUNTIF($C27:$G31,$O$9)=COUNTIF($C27:$G31,$P$9)=COUNTIF($C27:$G31,$Q$9)=COUNTIF($C27:$G31,$R$9)
Sheet1
Cell Formulas
RangeFormula
I15:I32I15=COUNTIF($C10:$G14,$I$9)
J15:J32J15=COUNTIF($C10:$G14,$J$9)
K15:K32K15=COUNTIF($C10:$G14,$K$9)
L15:L32L15=COUNTIF($C10:$G14,$L$9)
M15:M32M15=COUNTIF($C10:$G14,$M$9)
N15:N32N15=COUNTIF($C10:$G14,$N$9)
O15:O32O15=COUNTIF($C10:$G14,$O$9)
P15:P32P15=COUNTIF($C10:$G14,$P$9)
Q15:Q32Q15=COUNTIF($C10:$G14,$Q$9)
R15:R32R15=COUNTIF($C10:$G14,$R$9)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
I should also add that I prefer to have the value in each cell without the formula.
 
Upvote 0
Below is a Sub that looks like it should work for my request, but, it doesn't and I can not figure out why. It compiles without errors. When I run it in my worksheet it doesn't leave any values. It leaves nothing. No debug errors come up.

Any clue from a macro genius out there that can tell me what's wrong with the code?


VBA Code: 
Option Explicit

Sub CountIfL43()

Dim i As Integer
Dim j As Integer
Dim LastRow As Long
Dim LastColumn As Long

LastRow = Range("G" & Rows.Count).End(xlUp).Row
LastColumn = Range("A1").End(xlToRight).Column

For i = 10 To LastRow
For j = 9 To LastColumn
    Cells(i, LastColumn + 1).Value = _
        Application.CountIf(Range(Cells(i, 3).Offset(-43, 0), Cells(i, 7)), Cells(j, 9).Value)
Next j
Next i

Application.Calculation = xlCalculationAutomatic

End Sub
 
Last edited:
Upvote 0
Poker Joe said:
Below is a Sub that looks like it should work for my request, but, it doesn't and I can not figure out why. It compiles without errors. When I run it in my worksheet it doesn't leave any values. It leaves nothing. No debug errors come up.

Any clue from a macro genius out there that can tell me what's wrong with the code?


VBA Code: 
Option Explicit

Sub CountIfL43()

Dim i As Integer
Dim j As Integer
Dim LastRow As Long
Dim LastColumn As Long

LastRow = Range("G" & Rows.Count).End(xlUp).Row
LastColumn = Range("A1").End(xlToRight).Column

For i = 10 To LastRow
For j = 9 To LastColumn
    Cells(i, LastColumn + 1).Value = _
        Application.CountIf(Range(Cells(i, 3).Offset(-43, 0), Cells(i, 7)), Cells(j, 9).Value)
Next j
Next i

Application.Calculation = xlCalculationAutomatic

End Sub
Click to expand...
I ran this macro in a new worksheet and it did give me an Application defined or Object defined error for this line of code:

Cells(i, LastColumn + 1).Value = _
Application.CountIf(Range(Cells(i, 3).Offset(-43, 0), Cells(i, 7)), Cells(j, 9).Value)

My CountIf range should start at C10:G52. The range moves down one row each day, next day CountIf range would be C11:G53.

Any help would be appreciated. I also don't understand why I got no error or results in my original worksheet
 
Upvote 0
I'm not entirely sure that I have a full grip of what you have and what you are trying to do. However, I think this might be close.
Give it a test with a copy of your worksheet.

In the 'Const' line in the code I have used 5 which is what you seem to have used in the post 1 sample though it sounds like you might eventually want 43 there?

Just a side note: It is my understanding that vba converts Integer values to Long values to work with them, so simpler to declare them that way to start with.

VBA Code: 
Sub Test()
  Dim LastRow As Long, LastColumn As Long
  
  Const RowOffset As Long = 5 '43

  LastRow = Range("G" & Rows.Count).End(xlUp).Row + 2
  LastColumn = Range("I9").End(xlToRight).Column
  With Range("I10").Offset(RowOffset).Resize(LastRow - 9 - RowOffset, LastColumn - 8)
    .Formula = Replace("=COUNTIF($C10:$G#,I$9)", "#", 9 + RowOffset)
    .Value = .Value
  End With
End Sub


Before:

Poker Joe.xlsm
CDEFGHIJKLMNOPQRS
9C1C2C3C4C512345678910
1036958
11589105
1212422
13456610
14610277
1582438
1656952
1727661
18103474
1975186
2086417
212710110
2218943
2341366
2469574
25754101
26106734
27339910
2857559
2968223
3084485
31
32
33
Sheet3


After:

Poker Joe.xlsm
CDEFGHIJKLMNOPQRS
9C1C2C3C4C512345678910
1036958
11589105
1212422
13456610
14610277
15824381412442223
16569521513332313
17276611613342212
181034741412363212
19751861423244212
20864172323343311
2127101103213354211
22189434213145203
23413664124124313
24695745123143312
257541014124143222
261067344124233123
273399103035243122
28575592044243033
29682231033524043
30844851242423133
310243422332
320232411331
33
Sheet3
 
Upvote 0
Solution
Peter_SSs said:
I'm not entirely sure that I have a full grip of what you have and what you are trying to do. However, I think this might be close.
Give it a test with a copy of your worksheet.

In the 'Const' line in the code I have used 5 which is what you seem to have used in the post 1 sample though it sounds like you might eventually want 43 there?

Just a side note: It is my understanding that vba converts Integer values to Long values to work with them, so simpler to declare them that way to start with.

VBA Code: 
Sub Test()
  Dim LastRow As Long, LastColumn As Long
 
  Const RowOffset As Long = 5 '43

  LastRow = Range("G" & Rows.Count).End(xlUp).Row + 2
  LastColumn = Range("I9").End(xlToRight).Column
  With Range("I10").Offset(RowOffset).Resize(LastRow - 9 - RowOffset, LastColumn - 8)
    .Formula = Replace("=COUNTIF($C10:$G#,I$9)", "#", 9 + RowOffset)
    .Value = .Value
  End With
End Sub


Before:

Poker Joe.xlsm
CDEFGHIJKLMNOPQRS
9C1C2C3C4C512345678910
1036958
11589105
1212422
13456610
14610277
1582438
1656952
1727661
18103474
1975186
2086417
212710110
2218943
2341366
2469574
25754101
26106734
27339910
2857559
2968223
3084485
31
32
33
Sheet3


After:

Poker Joe.xlsm
CDEFGHIJKLMNOPQRS
9C1C2C3C4C512345678910
1036958
11589105
1212422
13456610
14610277
15824381412442223
16569521513332313
17276611613342212
181034741412363212
19751861423244212
20864172323343311
2127101103213354211
22189434213145203
23413664124124313
24695745123143312
257541014124143222
261067344124233123
273399103035243122
28575592044243033
29682231033524043
30844851242423133
310243422332
320232411331
33
Sheet3
Click to expand...
Peter,

That is perfect! I can not thank you enough. I also like the fact that I can easily change the:
Const RowOffset As Long = 5
Click to expand...
to count as many past rows as I'd like. I could even put in a MsgBox and another line of code asking how many past rows I'd like to go back.

This is awesome. THANK YOU!

MrExcel.com needs to implement a $ tip jar.
 
Upvote 0
Glad it worked well for you. Thanks for the follow-up. :)

Poker Joe said:
I could even put in a MsgBox and another line of code asking how many past rows I'd like to go back.
Click to expand...
Or another possibility would be to use a cell on the worksheet (eg I8) and have the code get the value from there.

Poker Joe said:
MrExcel.com needs to implement a $ tip jar.
Click to expand...
Good idea other than it would breach #5 of the Forum Rules ;)
The helpers here just like helping for the fun of it so there is definitely no need for money to get involved. It usually ends up causing problems anyway. 😎

BTW, Mark as solution should be the actual post that contains the solution, not your feedback message, so I have changed that for you.
 
Upvote 0
Peter_SSs said:
Glad it worked well for you. Thanks for the follow-up. :)


Or another possibility would be to use a cell on the worksheet (eg I8) and have the code get the value from there.


Good idea other than it would breach #5 of the Forum Rules ;)
The helpers here just like helping for the fun of it so there is definitely no need for money to get involved. It usually ends up causing problems anyway. 😎

BTW, Mark as solution should be the actual post that contains the solution, not your feedback message, so I have changed that for you.
Click to expand...
Good idea on using a cell vs. a MsgBox. The amount of time and frustration saved is worth more than a "Thank You!" in a reply, hence tip jar suggestion. I do understand the problems it could create. Thank You, Sir.
 
Upvote 0
Poker Joe said:
Good idea on using a cell vs. a MsgBox. The amount of time and frustration saved is worth more than a "Thank You!" in a reply, hence tip jar suggestion. I do understand the problems it could create. Thank You, Sir.
Click to expand...
Quick question. I tried to assign a cell value (H9) to the Const RowOffset As Long =, in a variety of ways but it keeps giving me an error. Do I need to change the Const RowOffSet assignment to something else?
 
Upvote 0
Poker Joe said:
Do I need to change the Const RowOffSet assignment to something else?
Click to expand...
Yes

Rich (BB code): 
Sub Test2()
  Dim LastRow As Long, LastColumn As Long
  Dim RowOffset As Long
  
  RowOffset = Range("H9").Value
  LastRow = Range("G" & Rows.Count).End(xlUp).Row + 2
  LastColumn = Range("I9").End(xlToRight).Column
  With Range("I10").Offset(RowOffset).Resize(LastRow - 9 - RowOffset, LastColumn - 8)
    .Formula = Replace("=COUNTIF($C10:$G#,I$9)", "#", 9 + RowOffset)
    .Value = .Value
  End With
 
Upvote 0
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
143
Fluff
Fluff
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
359
Asbestos_Jen
Asbestos_Jen
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
866
Cubist
Cubist
PuntingJawa
  • Solved
If column B has # then pull data from column A beside it
Replies
4
Views
223
PuntingJawa
PuntingJawa
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
90
MeaclH
M

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,623
Latest member
Techenthusiast

Share this page

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
Back
Top