Sumproduct counting blank cells, dates, capturing last row
 

Please assist.

There are several things I am trying to accomplish.

1) I am trying to count the blanks in several columns for each sheet.
2) I would like to count all the dates in a column.
3) I have a function that gives me the last row for every sheet. However I
would like to get the last row number to use in the sumproduct cell
reference.
for ex. sumproduct(j24:lastrow)


I tried the below to count the blanks in 2 columns.
What have I done wrong?


wsNew.Range("f7").Value =
Evaluate("=sumproduct((F25:f136=ax3)*(j24:j136="") )") +
sumproduct((F24:f2650=ax1)*(k24:k2650=""))")

Thanks

Sumproduct counting blank cells, dates, capturing last row
 

Hi Gwen,

In answer to your questions:
1) I am trying to count the blanks in several columns for each sheet.

The following works for one column and therefore call repeatedly for each
column.

function BlanksInColumn(c as string) as long
on error resume next
BlanksInColumn = Application.WorksheetFunction.CountBlank _
(ActiveSheet.Columns(c))
end function

2) I would like to count all the dates in a column.
function countdates(c as string)

Function countdates(ByVal c As String)
Dim i As Long
Dim nr As Long
Dim col As Range
On Error GoTo endline
c = c & "1" ' get a cell
Set col = Range(c).EntireColumn
c = 0
For i = 1 To 65536
If IsDate(col.Cells(i, 1)) Then
nr = nr + 1
End If
Next i
countdates = nr
endline:
Set col = Nothing
End Function


3) I have a function that gives me the last row for every sheet. However I
would like to get the last row number to use in the sumproduct cell
reference.
for ex. sumproduct(j24:lastrow)

isn't it the row number from the function you have?


--
Hope this helps
Martin Fishlock


"Gwen" wrote:

Please assist.

There are several things I am trying to accomplish.

1) I am trying to count the blanks in several columns for each sheet.
2) I would like to count all the dates in a column.
3) I have a function that gives me the last row for every sheet. However I
would like to get the last row number to use in the sumproduct cell
reference.
for ex. sumproduct(j24:lastrow)


I tried the below to count the blanks in 2 columns.
What have I done wrong?


wsNew.Range("f7").Value =
Evaluate("=sumproduct((F25:f136=ax3)*(j24:j136="") )") +
sumproduct((F24:f2650=ax1)*(k24:k2650=""))")

Thanks

Sumproduct counting blank cells, dates, capturing last row
 

Thank you for the solutions.
I guess I didn't explain my problem very well.
I have to count blanks and dates based on multiple criteria thats why I
used the sumproduct formula.

For ex. count the blanks for saleswoman a, saleswoman b, etc.
count the dates for saleswoman a, saleswoman b, etc.

"Martin Fishlock" wrote:

Hi Gwen,

In answer to your questions:
1) I am trying to count the blanks in several columns for each sheet.

The following works for one column and therefore call repeatedly for each
column.

function BlanksInColumn(c as string) as long
on error resume next
BlanksInColumn = Application.WorksheetFunction.CountBlank _
(ActiveSheet.Columns(c))
end function

2) I would like to count all the dates in a column.
function countdates(c as string)

Function countdates(ByVal c As String)
Dim i As Long
Dim nr As Long
Dim col As Range
On Error GoTo endline
c = c & "1" ' get a cell
Set col = Range(c).EntireColumn
c = 0
For i = 1 To 65536
If IsDate(col.Cells(i, 1)) Then
nr = nr + 1
End If
Next i
countdates = nr
endline:
Set col = Nothing
End Function


3) I have a function that gives me the last row for every sheet. However I
would like to get the last row number to use in the sumproduct cell
reference.
for ex. sumproduct(j24:lastrow)

isn't it the row number from the function you have?


--
Hope this helps
Martin Fishlock


"Gwen" wrote:

Please assist.

There are several things I am trying to accomplish.

1) I am trying to count the blanks in several columns for each sheet.
2) I would like to count all the dates in a column.
3) I have a function that gives me the last row for every sheet. However I
would like to get the last row number to use in the sumproduct cell
reference.
for ex. sumproduct(j24:lastrow)


I tried the below to count the blanks in 2 columns.
What have I done wrong?


wsNew.Range("f7").Value =
Evaluate("=sumproduct((F25:f136=ax3)*(j24:j136="") )") +
sumproduct((F24:f2650=ax1)*(k24:k2650=""))")

Thanks