ALTERNATIVE TO SUMPRODUCT NEEDED

[Guest]

Okay, but I still don't understand why if I add the word "BELLOW" to one of
the records and it goes through its 3 minutes of updating, it does not change
the number of "BELLOW" it sees. Any thoughts there?

 

[Harlan Grove]

Okay, but I still don't understand why if I add the word "BELLOW" to one
of the records and it goes through its 3 minutes of updating, it does not
change the number of "BELLOW" it sees. Any thoughts there?

You are adding BELLOW in col AD in records in which col X begins with
warranty? You are using the formula

=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty"),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$AD$45001)))

?

If so, the result should change.

 

[Don Guillett]

Here is the formula. I do have the auto calc set. I tried going manual and
using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$AD$45001)))

 

[Guest]

I made up a 10 record version of the DB and tried it there and it worked just
fine. Apparently there is a glitch in EXCEL because it seems to be related to
the numbr of records in the DB. This is a real concern; I don't know of any
way around this one. Thank you so much for all the help you have given.

 

[Harlan Grove]

....

....

Either you sent before adding text or you meant that the OP already stated
the problem. If the latter, here are the respective formulas w/o quoting.

Rog:
=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),
--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$AD$45001)))

me:
=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty"),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$AD$45001)))

Note the differences in the first SUMPRODUCT arg. The differences in the
second SUMPRODUCT are are irrelevant.

 

[Harlan Grove]

I made up a 10 record version of the DB and tried it there and it worked
just fine. Apparently there is a glitch in EXCEL because it seems to be
related to the numbr of records in the DB. This is a real concern;
I don't know of any way around this one. . . .

You could use a udf (user-defined function, written in VBA). It could be
more reliable processing huge ranges.


Function adhoc(r1 As Range, p1 As String, r2 As Range, p2 As String) As Long
Dim k As Long, n As Long

n = r1.Rows.Count

If n <> r2.Rows.Count Or r1.Columns.Count > 1 _
Or r2.Columns.Count > 1 Then
adhoc = -1 'error: return -1: ranges aren't 1D/same size
Exit Function
End If

For k = 1 To n
If CStr(r1.Cells(k, 1).Value) Like p1 _
And CStr(r2.Cells(k, 1).Value) Like p2 Then adhoc = adhoc + 1
Next k

End Function


Use it in formulas like

=adhoc(Portfolio_Review!$X$2:$X$45001,"warranty*",
Portfolio_Review!$AD$2:$AD$45001,"*BELLOW*")

Note that this udf does accept wildcards, and it even accepts character
classes. Read about VBA's Like operator in VBA online help. This will NOT
speed up recalc, but it may be more reliable.

 

[Guest]

EXACTLY. I just tested it again and it will not update. It takes a good 3
minutes to tell me that, but it doesn't.