SUMIF using 3D-reference
 

I have a series of spreadsheets, and I need to sum the hours only if they are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

SUMIF does exactly what you want, and this is a 2D reference. 3D references
apply data to the same positions across multiple pages.

=SUMIF($B$1000,"billable",$A$1000)

--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JeffPlax" wrote:

I have a series of spreadsheets, and I need to sum the hours only if they are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

Let me clear this up a little...

I want to use SUMIF like this

SUMIF(sheet1:sheet4!$B$2, "Yes", sheet1:sheet4!$A$2)

but it gives me the dreaded #VALUE

"JeffPlax" wrote:

I have a series of spreadsheets, and I need to sum the hours only if they are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

SUMIF(sheet1:sheet4!$B$2, "Yes", sheet1:sheet4!$A$2)

Are those your real sheet names?

If you have only 4 sheets involved:

=(Sheet1!B2="yes")*Sheet1!A2+(Sheet2!B2="Yes")*She et2!A2+(Sheet3!B2="Yes")*Sheet3!A2+(Sheet4!B2="Yes ")*Sheet4!A2

--
Biff
Microsoft Excel MVP


"JeffPlax" wrote in message
...
Let me clear this up a little...

I want to use SUMIF like this

SUMIF(sheet1:sheet4!$B$2, "Yes", sheet1:sheet4!$A$2)

but it gives me the dreaded #VALUE

"JeffPlax" wrote:

I have a series of spreadsheets, and I need to sum the hours only if they
are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

If you REALLY have a large range of cells to check, and not just one cell
like your sample, you will need to add some functionality to your sheets.

For a formula like this one to work:

=SUMIF3D2("Sheet1:Sheet3!B1:B20","yes","Sheet1:She et3!A1:A20")

Add the following two functions into a standard module:

===========
Function SumIf3D2(Range3D As String, Criteria As String, _
Optional Sum_Range As String) As Variant

Dim Sum As Double
Dim vaRng1 As Variant, vaRng2 As Variant
Dim i As Long

Application.Volatile

If Len(Sum_Range) = 0 Then
Sum_Range = Range3D
End If

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

Sum = 0
For i = LBound(vaRng1) To UBound(vaRng1)
Sum = Sum + Application.WorksheetFunction.SumIf(vaRng1(i), Criteria,
vaRng2(i))
Next i

SumIf3D2 = Sum

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
===========
Source:
http://www.dailydoseofexcel.com/arch...ned-functions/

Note, all of the parameters in that formula are strings, surrounded in
quotes. Watch for that.

Is this something you can use?

--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JeffPlax" wrote:

Let me clear this up a little...

I want to use SUMIF like this

SUMIF(sheet1:sheet4!$B$2, "Yes", sheet1:sheet4!$A$2)

but it gives me the dreaded #VALUE

"JeffPlax" wrote:

I have a series of spreadsheets, and I need to sum the hours only if they are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

See exemple:

http://cjoint.com/?bofTWZd7rT

Case 1: NamesSheets contains names of sheets to add:

=SUMPRODUCT(SUMIF(INDIRECT(NamesSheets&"!B2"),"Yes ",INDIRECT
(NamesSheets&"!A2")))

Case 2: Generic names Feuil1,Feuil2,....

=SUMPRODUCT(SUMIF(INDIRECT("Feuil"&ROW(INDIRECT("1 :4"))&"!B2"),"Yes",
INDIRECT("Feuil"&ROW(INDIRECT("1:4"))&"!A2")))

JB
http://boisgontierjacques.free.fr

On 13 jan, 22:11, JeffPlax wrote:
I have a series of spreadsheets, and I need to sum the hours only if they are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). *Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

Case 2: Generic names Feuil1,Feuil2,....

If there are only 4 sheets to reference you can replace this:

ROW(INDIRECT("1:4"))

With this:

{1,2,3,4}


--
Biff
Microsoft Excel MVP


"JB" wrote in message
...
See exemple:

http://cjoint.com/?bofTWZd7rT

Case 1: NamesSheets contains names of sheets to add:

=SUMPRODUCT(SUMIF(INDIRECT(NamesSheets&"!B2"),"Yes ",INDIRECT
(NamesSheets&"!A2")))

Case 2: Generic names Feuil1,Feuil2,....

=SUMPRODUCT(SUMIF(INDIRECT("Feuil"&ROW(INDIRECT("1 :4"))&"!B2"),"Yes",
INDIRECT("Feuil"&ROW(INDIRECT("1:4"))&"!A2")))

JB
http://boisgontierjacques.free.fr

On 13 jan, 22:11, JeffPlax wrote:
I have a series of spreadsheets, and I need to sum the hours only if they
are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

On 14 jan, 05:48, "T. Valko" wrote:
Case 2: Generic names Feuil1,Feuil2,....

If there are only 4 sheets to reference you can replace this:

ROW(INDIRECT("1:4"))

With this:

{1,2,3,4}

--
Biff
Microsoft Excel MVP

"JB" wrote in message

...
See exemple:

http://cjoint.com/?bofTWZd7rT

Case 1: NamesSheets contains names of sheets to add:

=SUMPRODUCT(SUMIF(INDIRECT(NamesSheets&"!B2"),"Yes ",INDIRECT
(NamesSheets&"!A2")))

Case 2: Generic names Feuil1,Feuil2,....

=SUMPRODUCT(SUMIF(INDIRECT("Feuil"&ROW(INDIRECT("1 :4"))&"!B2"),"Yes",
INDIRECT("Feuil"&ROW(INDIRECT("1:4"))&"!A2")))

JBhttp://boisgontierjacques.free.fr

I know!

http://boisgontierjacques.free.fr/pa...esMatricielles

JB

On 13 jan, 22:11, JeffPlax wrote:



I have a series of spreadsheets, and I need to sum the hours only if they
are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?- Masquer le texte des messages précédents -

- Afficher le texte des messages précédents -

SUMIF using 3D-reference
 

Hello,

Thanks for this. It works fine except when I close the file and re-open it.
Then I get the #NAME? error. Also, when I try to recreate the formula in the
same sheet the #NAME? error appears.

Any help would be great,

Steve

"JBeaucaire" wrote:

If you REALLY have a large range of cells to check, and not just one cell
like your sample, you will need to add some functionality to your sheets.

For a formula like this one to work:

=SUMIF3D2("Sheet1:Sheet3!B1:B20","yes","Sheet1:She et3!A1:A20")

Add the following two functions into a standard module:

===========
Function SumIf3D2(Range3D As String, Criteria As String, _
Optional Sum_Range As String) As Variant

Dim Sum As Double
Dim vaRng1 As Variant, vaRng2 As Variant
Dim i As Long

Application.Volatile

If Len(Sum_Range) = 0 Then
Sum_Range = Range3D
End If

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

Sum = 0
For i = LBound(vaRng1) To UBound(vaRng1)
Sum = Sum + Application.WorksheetFunction.SumIf(vaRng1(i), Criteria,
vaRng2(i))
Next i

SumIf3D2 = Sum

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
===========
Source:
http://www.dailydoseofexcel.com/arch...ned-functions/

Note, all of the parameters in that formula are strings, surrounded in
quotes. Watch for that.

Is this something you can use?

--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JeffPlax" wrote:

Let me clear this up a little...

I want to use SUMIF like this

SUMIF(sheet1:sheet4!$B$2, "Yes", sheet1:sheet4!$A$2)

but it gives me the dreaded #VALUE

"JeffPlax" wrote:

I have a series of spreadsheets, and I need to sum the hours only if they are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

My apologies.

It was the secuity setting on the file! I'm afraid I'm not very experienced
with macros.

Thanks again for your code.

Steve

"JBeaucaire" wrote:

If you REALLY have a large range of cells to check, and not just one cell
like your sample, you will need to add some functionality to your sheets.

For a formula like this one to work:

=SUMIF3D2("Sheet1:Sheet3!B1:B20","yes","Sheet1:She et3!A1:A20")

Add the following two functions into a standard module:

===========
Function SumIf3D2(Range3D As String, Criteria As String, _
Optional Sum_Range As String) As Variant

Dim Sum As Double
Dim vaRng1 As Variant, vaRng2 As Variant
Dim i As Long

Application.Volatile

If Len(Sum_Range) = 0 Then
Sum_Range = Range3D
End If

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

Sum = 0
For i = LBound(vaRng1) To UBound(vaRng1)
Sum = Sum + Application.WorksheetFunction.SumIf(vaRng1(i), Criteria,
vaRng2(i))
Next i

SumIf3D2 = Sum

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
===========
Source:
http://www.dailydoseofexcel.com/arch...ned-functions/

Note, all of the parameters in that formula are strings, surrounded in
quotes. Watch for that.

Is this something you can use?

--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JeffPlax" wrote:

Let me clear this up a little...

I want to use SUMIF like this

SUMIF(sheet1:sheet4!$B$2, "Yes", sheet1:sheet4!$A$2)

but it gives me the dreaded #VALUE

"JeffPlax" wrote:

I have a series of spreadsheets, and I need to sum the hours only if they are
billable

column A contains hours, and column B indicates weather hours in column A
are billable or not (Yes or No). Does SUMIF work using 3D-references?

SUMIF using 3D-reference
 

This can be done with worksheet functions. Do your sheet names follow some
sort of sequential naming pattern? For example: Week1, Week2, Week3, etc?

The easiest way to do this is to put a SUMIF on each individual sheet in the
same cell. Then on your summary sheet use a SUM function like this:

=SUM(Sheet1:Sheet10!A1)

--
Biff
Microsoft Excel MVP


"Steve1964" wrote in message
...
Hello,

Thanks for this. It works fine except when I close the file and re-open
it.
Then I get the #NAME? error. Also, when I try to recreate the formula in
the
same sheet the #NAME? error appears.

Any help would be great,

Steve

"JBeaucaire" wrote:

If you REALLY have a large range of cells to check, and not just one cell
like your sample, you will need to add some functionality to your sheets.

For a formula like this one to work:

=SUMIF3D2("Sheet1:Sheet3!B1:B20","yes","Sheet1:She et3!A1:A20")

Add the following two functions into a standard module:

===========
Function SumIf3D2(Range3D As String, Criteria As String, _
Optional Sum_Range As String) As Variant

Dim Sum As Double
Dim vaRng1 As Variant, vaRng2 As Variant
Dim i As Long

Application.Volatile

If Len(Sum_Range) = 0 Then
Sum_Range = Range3D
End If

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

Sum = 0
For i = LBound(vaRng1) To UBound(vaRng1)
Sum = Sum + Application.WorksheetFunction.SumIf(vaRng1(i),
Criteria,
vaRng2(i))
Next i

SumIf3D2 = Sum

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
===========
Source:
http://www.dailydoseofexcel.com/arch...ned-functions/

Note, all of the parameters in that formula are strings, surrounded in
quotes. Watch for that.

Is this something you can use?

--
"Actually, I AM a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JeffPlax" wrote:

Let me clear this up a little...

I want to use SUMIF like this

SUMIF(sheet1:sheet4!$B$2, "Yes", sheet1:sheet4!$A$2)

but it gives me the dreaded #VALUE

"JeffPlax" wrote:

I have a series of spreadsheets, and I need to sum the hours only if
they are
billable

column A contains hours, and column B indicates weather hours in
column A
are billable or not (Yes or No). Does SUMIF work using
3D-references?