Possible SUMPRODUCT use?

[Phosphonothioic]

I have entries in column A. Some are 3 characters long, some are 4. Then I have values associated to them in column B

Example
A B
A100 1
A200 3
B70 4
C455 2
X35 3
B27 2


I want to do the following:

Look at each entry in A and if it is 3 characters long sum the values in B based on the first character in A. So, I would want to return '6' when I evaluated this list for 3 charter entries starting with 'B'. And 3 if I changed the parameter to 'X'.

Is there some type of sumproduct/len/? that will do this?

Thanks!

 

[Jonmo1]

Try

=SUMPRODUCT(--(LEFT(A2:A100,1)="B"),--(LEN(A2:A100)=3),B2:B100)


Hope that helps.

 

[Aladin Akyurek]

I have entries in column A. Some are 3 characters long, some are 4. Then I have values associated to them in column B

Example
A B
A100 1
A200 3
B70 4
C455 2
X35 3
B27 2


I want to do the following:

Look at each entry in A and if it is 3 characters long sum the values in B based on the first character in A. So, I would want to return '6' when I evaluated this list for 3 charter entries starting with 'B'. And 3 if I changed the parameter to 'X'.

Is there some type of sumproduct/len/? that will do this?

Thanks!

Try...

=SUMPRODUCT($B$2:$B$7,--(LEN($A$2:$A$7)=3),--(LEFT($A$2:$A$7,1)="B"))

 

[BobUmlas]

If the varying cell is in E1 (where the "B" or "X" might be stored):
=SUMPRODUCT(N(LEN(A1:A6)=3),N(LEFT(A1:A6,1)=E1),B1:B6)
Or without using E1:
=SUMPRODUCT(N(LEN(A1:A6)=3),N(LEFT(A1:A6,1)="B"),B1:B6)

 

[Phosphonothioic]

If the varying cell is in E1 (where the "B" or "X" might be stored):
=SUMPRODUCT(N(LEN(A1:A6)=3),N(LEFT(A1:A6,1)=E1),B1:B6)
Or without using E1:
=SUMPRODUCT(N(LEN(A1:A6)=3),N(LEFT(A1:A6,1)="B"),B1:B6)

The varying factor is the first character in A1. It'll be some letter of the alphabet. Only A and B have information in them.

 

[BobUmlas]

use the 2nd version of the solution provided. I DID assume the data was only in A & B, but thought it'd be easier to get the result by storing the leading letter in a different cell (E1) rather than having to change the character in the formula.

Check out my new book at http://www.mrexcel.com/oursidethebox.html

 

[barry houdini]

Try SUMIF

=SUMIF(A:A,"B??",B:B)