As usual, there's more than one way to skin a cat!


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"T. Valko" wrote:

Try one of these:

=SUMPRODUCT((A1:A6="approved")+(A1:A6="wholesale") ,--(ISNUMBER(SEARCH("lease",B1:B6))))

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A6,{"approved","wholesale"},0)) ),--(ISNUMBER(SEARCH("lease",B1:B6))))

Better to use cells to hold the ctriteria...

D1 = approved
E1 = wholesale
F1 = lease

=SUMPRODUCT((A1:A6=D1)+(A1:A6=E1),--(ISNUMBER(SEARCH(F1,B1:B6))))

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A6,D1:E1,0))),--(ISNUMBER(SEARCH(F1,B1:B6))))

Note that with SUMPRODUCT you *can't* use entire columns as range references
unless you're using Excel 2007.

--
Biff
Microsoft Excel MVP


"DSKR" wrote in message
...
I'm having a problem with SumProduct when Column B contains the word
"Lease".
Here's what I want to count: Number of "Lease" IF Column A is "Approved"
or
"Wholesale". I think the problem is the additional information after
"Lease"
in Column B. I've tried using asterisks, but can't seem to find the right
formula. Any ideas?


Column A Column B
Approved M1 Gate/Free/USB/3 Day
Wholesale Pur/A/FS
Approved Lease/Wireless/3-5
Approved Lease/FD 100/3-5
Declined M1 Gate
Approved DP