vba criteria syntax in countifs formula

0
votes

I've got data stored on multiple worksheets in Excel. Each worksheets has the exact same data structure.
I am trying to make a script, that makes me a nice summery view on another worksheet.
This worksheet has the worksheet names listed on column 1 and I want to count rows that satisfy specific conditions. I managed to get a countifs function produced that counts rows for which a specific column has the TRUE logical value, but I have date and time values as well, and I want to count ones between two specific dates. What do I get wrong?

for i = [got the range right here]
    wsOverview.cells(i, 2).Formula = "=countifs(" & ThisWorkbook.sheets(wsOverview.cells(i, 1).value).Range("AI:AI").Address(external:=True) & ","">""" & wsOverview.cells(1,2).value & ")"
next i

(this is an attempt at the date greater than criteria, I don't bother with the maximal date value until i manage to get this right.)

The problem is somewhere in the last part, because replacing "">""" & wsOverview.cells(1,2).value with TRUE and the AIs with the column that has boolean data yields results.

2
excelvbaexcel-formulacountif
Store the date(s) in cells on a sheet, and have & ", "">"" & A1)"? - GSerg
I was missing the & sign from before the cell reference in the formula. Similar usage of the same & sign in the VBA script and the actual excel cell formula must have confused me. This has helped, thank you! You might as well add it as an answer. I'm aware that I could, but I'm new here and I don't want to steal your bug-hunting glory! Alternatively, I'll do it if you want me to, @GSerg. - Áron
Please feel free to post an answer. - GSerg

2 Answers

0
votes

Based on GSerg's remark, I've managed to get this right.

It was a problem with & signs, I only used the ones that tied together the VBA code, but forgot the one that made the cell formula working.

I've done something along the lines replacing

"">""" &

with

"">""&" &

resulting the cell having one of those in the countifs function properly.

=countifs(proper criteria range; ">"&B2)
-1
votes

Try this (A1 contents first date, B1 - second date):

Sub frm()
    Set wsOverview = Worksheets("over")
    template = "=COUNTIFS(INDIRECT(#&""!B:B""),"">""&over!A1,INDIRECT(#&""!B:B""),""<""&over!B1)"
    For i = 2 To 4
        wsOverview.Cells(i, 2).Formula = Replace(template, "#", wsOverview.Cells(i, 1).Address)
    Next i
End Sub