|
Sumproduct help again
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 |
Sumproduct help again
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 |
Sumproduct help again
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "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 |
Sumproduct help again
That worked! Thanks!!!
"ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "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 |
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 |
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 |
Sumproduct help again
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 |
| All times are GMT +1. The time now is 06:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com