Thread
:
Sumproduct help again
View Single Post
#
6
Posted to microsoft.public.excel.worksheet.functions
Don Guillett
external usenet poster
Posts: 10,124
Sumproduct help again
Try this idea
=SUMPRODUCT((LEFT(A2:A22,5)={"Appro","Whole"})*(LE FT(B2:B22,5)="Lease"))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"DSKR" wrote in message
...
I've tried:
=SUMPRODUCT(--((A:A="Approved")+(A:A="Wholesale")),--((B:B="Lease")))
=SUMPRODUCT(--((A:A="Approved")+(A:A="Wholesale")),--((B:B="Lease*")))
=SUMPRODUCT(--((A:A="Approved")+(A:A="Wholesale")),--((B:B="*Lease*")))
=SUMPRODUCT(--((A:A="Approved")+(A:A="Wholesale")),--((B:B="Lease/Wireless")+(B:B="Lease/A")+(B:B="Lease/D")))
The last covers all three options that would occur after the word "Lease".
Thanks.
"Don Guillett" wrote:
Where is YOUR effort
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"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
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett