Sumproduct help again

[DSKR]

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

 

[Don Guillett]

Where is YOUR effort

 

[DSKR]

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.

 

[ryguy7272]

I would do it like this
=SUMPRODUCT(--((A26:A31="Approved")),--ISNUMBER(SEARCH("*Lease*",B26:B31)))+SUMPRODUCT(--((A26:A31="Wholesale")),--ISNUMBER(SEARCH("*Lease*",B26:B31)))

HTH,
Ryan---

 

[Don Guillett]

Try this idea

=SUMPRODUCT((LEFT(A2:A22,5)={"Appro","Whole"})*(LEFT(B2:B22,5)="Lease"))

 

[T. Valko]

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.

 

[ryguy7272]

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