# Possible SUMPRODUCT use?

#### Phosphonothioic

##### Board Regular
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!

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Try

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

Hope that helps.

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"))

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)

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.

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

Try SUMIF

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

Replies
1
Views
193
Replies
1
Views
80
Replies
7
Views
454
Replies
3
Views
191
Replies
2
Views
163

### Forum statistics

1,206,921
Messages
6,075,581
Members
446,147
Latest member
homedecortips ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back