Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct
col a col b answer
3sha000011 0124 3sha & 0 =1 3sha000012 2336 3sha & 2 =1 4sha000013 0495 4sha & 0 =2 4sha000002 0113 if col a begins with 3sha and col b begins with 0 count answer 1 if col a begins with 4sha and col b begins with 0 count answer 2 I tried countif(and(left(a2:a5,4)="3sha",b2:b5,1)="0")) and couldn't get it to work. i have 8000+ in col a and 8000+ in col b |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct
Hi,
Am Sat, 11 Aug 2012 03:38:23 +0000 schrieb old coach: col a col b answer 3sha000011 0124 3sha & 0 =1 3sha000012 2336 3sha & 2 =1 4sha000013 0495 4sha & 0 =2 4sha000002 0113 if col a begins with 4sha and col b begins with 0 count answer 2 if you use xl 2007 0r 2010 then: =COUNTIFS(A2:A5,"4sha*",B2:B5,"0*") for earlier versions: =SUMPRODUCT(--(LEFT(A2:A5,4)="4sha"),--(LEFT(B2:B5,1)="0")) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Quote:
add one 2 more conditions, same as B2:B5. if cell has "J 0" as the lead characters or "Y 0" It will work separately but when i add it to the above formula as a 3rd & 4th condition, it shows 0 as answer. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
sumproduct
Hi,
Am Sat, 11 Aug 2012 17:39:13 +0000 schrieb old coach: add one 2 more conditions, same as B2:B5. if cell has "J 0" as the lead characters or "Y 0" It will work separately but when i add it to the above formula as a 3rd & 4th condition, it shows 0 as answer. I hope, i understood your problem correctly. Try: =SUMPRODUCT((LEFT(A2:A5,4)="4sha")*((LEFT(B2:B5,3) ="Y 0")+(LEFT(B2:B5,3)="J 0")+(LEFT(B2:B5,1)="0"))) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct instead of SumifS in VBA (application.sumproduct) | Excel Programming | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
SUM IF SUMPRODUCT | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions |