Sumproduct help again
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
|