Using SUMPRODUCT Function on Calculated (Function-Derived) Data
 

I have a spreadsheet (called Monday in the sample below) with the
following columns:

JobNumber (Col D); RequiresTimeSheet (Col E); Times (Col M); Hours
(Col N)

Formatted as follows:

"AAAA";"Yes"/"No", "AAAA-AAAA", [CalculatedHours]

Where the first three columns are strings. The "Times" column is also
a string representing military time (Ex: "0700-0930"). A function is
used to calculate the number of hours (Col. N) worked based on the
"Times" entered (Ex: 2.5 for 0700-0930). This column is in a number
format, not a string.

I am using the SumProduct function as follows:

=SUMPRODUCT((Monday!D2:D15="7557")*(Monday!E2:E15= "Yes")*(Monday!
N2:N15))

With the expectation that the result would be the total number of
calculated hours in the N2:N15 column which are JobNumber "7557" and
"Yes" marked in the "RequiresTimeSheet" column.

There seems to be an issue with the fact that column N (Hours) is a
calculated column. If I run the above, but actually type values in to
column N, it works fine. However, the calculated nature of this
column does not seem to be a problem for other functions such as SUM.

Does this sound familiar to anyone and is there any way this can be
corrected (short of Special Pasting Values Only, which is not an
option)?

Thank you!

Using SUMPRODUCT Function on Calculated (Function-Derived) Data
 

Hi

Maybe your formula in column N is not returning a true value, if that is the
case you can use this:

=value(your_formula)

Regards,
Per

"jtertin" skrev i meddelelsen
...
I have a spreadsheet (called Monday in the sample below) with the
following columns:

JobNumber (Col D); RequiresTimeSheet (Col E); Times (Col M); Hours
(Col N)

Formatted as follows:

"AAAA";"Yes"/"No", "AAAA-AAAA", [CalculatedHours]

Where the first three columns are strings. The "Times" column is also
a string representing military time (Ex: "0700-0930"). A function is
used to calculate the number of hours (Col. N) worked based on the
"Times" entered (Ex: 2.5 for 0700-0930). This column is in a number
format, not a string.

I am using the SumProduct function as follows:

=SUMPRODUCT((Monday!D2:D15="7557")*(Monday!E2:E15= "Yes")*(Monday!
N2:N15))

With the expectation that the result would be the total number of
calculated hours in the N2:N15 column which are JobNumber "7557" and
"Yes" marked in the "RequiresTimeSheet" column.

There seems to be an issue with the fact that column N (Hours) is a
calculated column. If I run the above, but actually type values in to
column N, it works fine. However, the calculated nature of this
column does not seem to be a problem for other functions such as SUM.

Does this sound familiar to anyone and is there any way this can be
corrected (short of Special Pasting Values Only, which is not an
option)?

Thank you!

Using SUMPRODUCT Function on Calculated (Function-Derived) Data
 

I'm closer, but I still have a problem:

Previously, I was using a VBA function to calculate the number of
hours from the military times given.

I updated the Hours column (Col N), to calculate the number of hours
using the following formula: =IF(L2="","",(TEXT(RIGHT(L2,4),"00\:00")-
TEXT(LEFT(L2,4),"00\:00"))*24)

The importaint part being "(TEXT(RIGHT(L2,4),"00\:00")-TEXT(LEFT
(L2,4),"00\:00"))*24". The IF is just to prevent #VALUE from showing
if there is nothing entered into the Times column (Col M).

While taking this approach, I CAN use the following formula in a
worksheet cell: =SUMPRODUCT((Monday!D2:D6="W7557")*(Monday!E2:E6=" Yes")
*(Monday!M2:M6))

However, while I try to run this formula from VBA, I get a Type
Mismatch error. This error can be prevented if I actually ENTER
NUMBERS into the hours column (Col M).

The VBA function is as follows:

Public Function DailyHours(JobNumber As String, strDay As String) As
Double
Dim rngJobNumbers As Range
Dim rngReqsTimeSheet As Range
Dim rngHours As Range
Dim s As String
Dim vResult As Variant

Set rngJobNumbers = Sheets(strDay).Range("D2:D35")
Set rngReqsTimeSheet = Sheets(strDay).Range("E2:E35")
Set rngHours = Sheets(strDay).Range("M2:M35")

vResult = Evaluate("SumProduct(((" & rngJobNumbers.Address & ") = """
& JobNumber & """) * " & "((" & rngReqsTimeSheet.Address & ") =
""Yes"") * " & "(" & rngHours.Address & "))")

DailyHours=vResult

End Function

I assume VBA is trying to evaluate the function using the formulas
which appear in the "rngHours" range.

That being said, the question:

How can I have VBA evaluate this range based on the VALUES of the
range and not the formulas?

Using SUMPRODUCT Function on Calculated (Function-Derived) Dat
 

Have you tried using Excel.WorksheetFunction.SumProduct() instead of
Evaluate("SumProduct(... ?

--
Powered by Creative Laziness


"jtertin" wrote:

I'm closer, but I still have a problem:

Previously, I was using a VBA function to calculate the number of
hours from the military times given.

I updated the Hours column (Col N), to calculate the number of hours
using the following formula: =IF(L2="","",(TEXT(RIGHT(L2,4),"00\:00")-
TEXT(LEFT(L2,4),"00\:00"))*24)

The importaint part being "(TEXT(RIGHT(L2,4),"00\:00")-TEXT(LEFT
(L2,4),"00\:00"))*24". The IF is just to prevent #VALUE from showing
if there is nothing entered into the Times column (Col M).

While taking this approach, I CAN use the following formula in a
worksheet cell: =SUMPRODUCT((Monday!D2:D6="W7557")*(Monday!E2:E6=" Yes")
*(Monday!M2:M6))

However, while I try to run this formula from VBA, I get a Type
Mismatch error. This error can be prevented if I actually ENTER
NUMBERS into the hours column (Col M).

The VBA function is as follows:

Public Function DailyHours(JobNumber As String, strDay As String) As
Double
Dim rngJobNumbers As Range
Dim rngReqsTimeSheet As Range
Dim rngHours As Range
Dim s As String
Dim vResult As Variant

Set rngJobNumbers = Sheets(strDay).Range("D2:D35")
Set rngReqsTimeSheet = Sheets(strDay).Range("E2:E35")
Set rngHours = Sheets(strDay).Range("M2:M35")

vResult = Evaluate("SumProduct(((" & rngJobNumbers.Address & ") = """
& JobNumber & """) * " & "((" & rngReqsTimeSheet.Address & ") =
""Yes"") * " & "(" & rngHours.Address & "))")

DailyHours=vResult

End Function

I assume VBA is trying to evaluate the function using the formulas
which appear in the "rngHours" range.

That being said, the question:

How can I have VBA evaluate this range based on the VALUES of the
range and not the formulas?

Using SUMPRODUCT Function on Calculated (Function-Derived) Dat
 

That didn't appear to work either, but I did find something extremely
strange:

When I run the subroutine above, I get the usual Type Mismath error.

However, I stumbled upon the strange thing:

To verify that my string was correct, I put a breakpoint at the
Evaluate statement (so I can verify the value of the string I am
evaluating). The strange part is that the Evaluate will error out, so
I copy the string value from the watch window and paste it into an
IDENTICALLY FORMATTED, BUT SEPARATE WORKBOOK, and it works fine.
THEN, if I return to VBA and drag the yellow arrow up one step so the
Evaluate is executed again, IT WORKS.

Does this make sense?

Using SUMPRODUCT Function on Calculated (Function-Derived) Data
 

Hi

Did a little testing, and found that you have to change your formulas a bit
to make it work:

=IF(L2="",0,(TEXT(RIGHT(L2,4),"00\:00")-TEXT(LEFT(L2,4),"00\:00"))*24)

Sumproduct return an error for elements in column N which is 'calcuated' as
blank, so in the formula above the formula return a zero if L2 is empty.

If the numbers in column D are true numbers, do not use quotation signs
around 7557, also I substituted the * sign with , and inserted a double
unary before each statement. By doing this I can evaluate the function by
clicking the equal sign left to the formula line and see the result for each
statement.

=SUMPRODUCT(--(Monday!D2:D15=7557),--(Monday!E2:E15="Yes"),--(Monday!N2:N15))

Best regards,
Per


"jtertin" skrev i meddelelsen
...
I'm closer, but I still have a problem:

Previously, I was using a VBA function to calculate the number of
hours from the military times given.

I updated the Hours column (Col N), to calculate the number of hours
using the following formula: =IF(L2="","",(TEXT(RIGHT(L2,4),"00\:00")-
TEXT(LEFT(L2,4),"00\:00"))*24)

The importaint part being "(TEXT(RIGHT(L2,4),"00\:00")-TEXT(LEFT
(L2,4),"00\:00"))*24". The IF is just to prevent #VALUE from showing
if there is nothing entered into the Times column (Col M).

While taking this approach, I CAN use the following formula in a
worksheet cell: =SUMPRODUCT((Monday!D2:D6="W7557")*(Monday!E2:E6=" Yes")
*(Monday!M2:M6))

However, while I try to run this formula from VBA, I get a Type
Mismatch error. This error can be prevented if I actually ENTER
NUMBERS into the hours column (Col M).

The VBA function is as follows:

Public Function DailyHours(JobNumber As String, strDay As String) As
Double
Dim rngJobNumbers As Range
Dim rngReqsTimeSheet As Range
Dim rngHours As Range
Dim s As String
Dim vResult As Variant

Set rngJobNumbers = Sheets(strDay).Range("D2:D35")
Set rngReqsTimeSheet = Sheets(strDay).Range("E2:E35")
Set rngHours = Sheets(strDay).Range("M2:M35")

vResult = Evaluate("SumProduct(((" & rngJobNumbers.Address & ") = """
& JobNumber & """) * " & "((" & rngReqsTimeSheet.Address & ") =
""Yes"") * " & "(" & rngHours.Address & "))")

DailyHours=vResult

End Function

I assume VBA is trying to evaluate the function using the formulas
which appear in the "rngHours" range.

That being said, the question:

How can I have VBA evaluate this range based on the VALUES of the
range and not the formulas?