| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumproduct help again
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
| sumproduct | Excel Discussion (Misc queries) | |||
| SUMPRODUCT except for | New Users to Excel | |||
| sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
| Sumproduct | Excel Worksheet Functions | |||
Linear Mode
