Countif 3D

[Joanne]

Hello again

I know I've been posting a lot, but the workbook I'm currently working on
has no end of problems, and is probably beyond my capabilities. But there, I
have to do it! So I really appreciate any help that you could give me.

I'm trying to use a UDF (that was posted a while ago, so I can't ask the
author)
that I found on:

http://www.dailydoseofexcel.com/archives/2006/02/25/3d-user-defined-functions/

I want to Count every time a name appears in the same selection of cells
across multiple worksheets. I have copied the code in, and plugged in the
numbers but I get #VALUE! returned. My only theory is that some cells will be
empty and that confuses excel? What am I doing wrong!?!?

Thanks in advance!

The formula I enter is: =CountIf3D2('Blank 1:Blank 2'!A9:A10;A43)

The code relating to CountIf3D2 is as follows ( I think it also utilises the
function Parse3DRange2, which is also pasted below):

Function CountIf3D2(Range3D As String, Criteria As String) _
As Variant

Dim i As Long
Dim Count As Long
Dim vaRng1 As Variant

Application.Volatile

vaRng1 = Parse3DRange2(Application.Caller.Parent.Parent, Range3D)

Count = 0
For i = LBound(vaRng1) To UBound(vaRng1)
Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i),
Criteria)
Next i

CountIf3D2 = Count

End Function

Function Parse3DRange2(wb As Workbook, _
SheetsAndRange As String) As Variant

Dim sTemp As String
Dim i As Long, j As Long
Dim Sheet1 As String, Sheet2 As String
Dim aRange() As Range
Dim sRange As String
Dim lFirstSht As Long, lLastSht As Long
Dim rCell As Range
Dim rTemp As Range

On Error GoTo Parse3DRangeError

sTemp = SheetsAndRange

'if it's 3d, rtemp will be nothing
On Error Resume Next
Set rTemp = Range(sTemp)
On Error GoTo Parse3DRangeError

'if it's 3d, parse it
If rTemp Is Nothing Then
i = InStr(sTemp, "!")
If i = 0 Then Err.Raise 9999

'next line will generate an error if range is invalid
'if it's OK, it will be converted to absolute form
sRange = Range(Mid$(sTemp, i + 1)).Address

sTemp = Left$(sTemp, i - 1)
i = InStr(sTemp, ":")
Sheet2 = Trim(Mid$(sTemp, i + 1))
If i> 0 Then
Sheet1 = Trim(Left$(sTemp, i - 1))
Else
Sheet1 = Sheet2
End If

'next lines will generate errors if sheet names are invalid
With wb
lFirstSht = .Worksheets(Sheet1).Index
lLastSht = .Worksheets(Sheet2).Index

'swap if out of order
If lFirstSht> lLastSht Then
i = lFirstSht
lFirstSht = lLastSht
lLastSht = i
End If

'load each cell into an array
j = 0
For i = lFirstSht To lLastSht
For Each rCell In .Sheets(i).Range(sRange)
ReDim Preserve aRange(0 To j)
Set aRange(j) = rCell
j = j + 1
Next rCell
Next i
End With

Parse3DRange2 = aRange
Else
'range isn't 3d, so just load each cell into array
For Each rCell In rTemp.Cells
ReDim Preserve aRange(0 To j)
Set aRange(j) = rCell
j = j + 1
Next rCell

Parse3DRange2 = aRange
End If

Parse3DRangeError:
On Error GoTo 0
Exit Function

End Function 'Parse3DRange

Category: Excel Experts E-Letter | Comment (RSS) | Trackback

 

[Bernie Deitrick]

Joanne,

The Original code doesn't properly handle sheet names with spaces. Either rename your sheets to
take out the spaces, or change

Sheet2 = Trim(Mid$(sTemp, i + 1))
If i > 0 Then
Sheet1 = Trim(Left$(sTemp, i - 1))


to

Sheet2 = Replace(Trim(Mid$(sTemp, i + 1)), "'", "")
If i > 0 Then
Sheet1 = Replace(Trim(Left$(sTemp, i - 1)), "'", "")



HTH,
Bernie
MS Excel MVP

 

[Dave Peterson]

I'd check your data first.

The majority of that code parses the formula to figure out what worksheets (and
ranges) should be used.

The line that does the "real" work is this:

Count = Count + Application.WorksheetFunction.CountIf(vaRng1(i), Criteria)

The first thing I would do is build an equivalent =countif() in an empty cell on
each worksheet--just for that worksheet.

Do you see a number returned in each of those formulas? (I'd look for errors in
any of the ranges in any of the worksheets.)

 

[Joanne]

Thank you, I made the change to the code you sugested.
However when testing, although I now no longer get "#VALUE!", I get a zero
when I know there should be 4.

 

[Bernie Deitrick]

Joanne,

Make sure that your actual values return something other than zero (meybe, for example, you have an
extra space somewhere). Use COUNTIF with the same parameters except for just one sheet:

=COUNTIF('Blank 1'!A9:A10;A43)

Or, if Blank 1 is a "Bookend" sheet, replace it with a sheet name that has data.

HTH,
Bernie
MS Excel MVP

 

[Joanne]

Yes the range has a start/end bookend (Blank 1,Blank 2), I tried with just
one of the middle ones which does have data:

=CountIf3D2("'Product 1'!A9:A10";"A43")

And still recieve zero when I know there should be 2.
(It would be very difficult for me to remove spaces in the names of
worksheets because this is a template that will be used by many others. I
have a macro that creates new tabs on a key command depending on something
they enter in the summary sheet.)

 

[Bernie Deitrick]

Joanne,

A43 should not be in quotes, unless you area actually looking for the string "A43". If you are
looking for the value that matches the string entered in cell A43, then use A43 without the quotes.

HTH,
Bernie
MS Excel MVP

 

[Joanne]

Perfect! It even works across the range of worksheets. I cannot tell you how
much this will help me, thank you very much!