Using SUMPRODUCT Function on Calculated (Function-Derived) Data

[jtertin]

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!D215="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!

 

[Per Jessen]

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]

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!D26="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("D235")
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?

 

[jtertin]

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?

 

[Per Jessen]

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!D215=7557),--(Monday!E2:E15="Yes"),--(Monday!N2:N15))

Best regards,
Per

 

[jtertin]

Thank you SO much for your time and effort in solving this issue.
That was exactly what the problem was.

It's still really strange that it worked after pasting the formula
into a separate workbook and then re-evaluating the SumProduct formula
in VBA actually returned the correct value even though the cells
didn't change.

I really appreciate it. Got a PayPal account so I can buy you a beer
or something?

-John

 

[Per Jessen]

Glad you solved it.

Not sure why it worked after you pasted it to another sheet unless you
only pasted values in column N.

Yes I do have a PayPal account, but don't feel bound to use it.
(e-mail address removed)

Best regards,
Per