#1   Report Post  
Junior Member
 
Posts: 14
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Junior Member
 
Posts: 14
Default

Quote:
Originally Posted by Claus Busch View Post
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
Great! It worked. Let's go one step futher.

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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct instead of SumifS in VBA (application.sumproduct) Majken Bilslev-Jensen Excel Programming 7 December 30th 10 06:56 PM
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Excel Discussion (Misc queries) 2 September 28th 09 05:07 PM
SUM IF SUMPRODUCT mparker Excel Discussion (Misc queries) 2 April 21st 09 12:32 PM
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Excel Worksheet Functions 4 August 14th 08 07:50 PM
sumproduct? sumif(sumproduct)? David Excel Worksheet Functions 3 July 13th 07 07:06 PM


All times are GMT +1. The time now is 08:19 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"