# =SUMIF Help

##### Active Member
Greetings Board.

XP Pro; Excel 2003

Here's what I'm trying to do.

if A1:A10 and left (B1:B10,2) = D1 & E1 then sum C1:C10

Here's my formula = SUMIF(A1:A10 & LEFT(B1:B10,2),D1&E1,C1:C10)

Can't get it to take, either as a formula or as an array.

I may need to use sumproduct. I would need help with sumproduct as I'm still learning to use sumproduct.

I would rather not add any more helper columns, but I know I can concatenate this data in the helper columns and then use a simple sumif formula with a single condition for the first argument.

Any thoughts?

THANK YOU.

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Jonmo1

##### MrExcel MVP
Does this work...

=SUMPRODUCT(--(A1:A10&LEFT(B1:B10,2)=D1&E1),C1:C10)

##### MrExcel MVP
In a cautious set up and assuming that E1 must hold for the range B...

Code:
``````=SUMPRODUCT(
--(\$A\$1:\$A\$10&"#"&LEFT(\$B\$1:\$B\$10,2)=D1&"#"&E1),
\$C\$1:\$C\$10)``````

Greetings Board.

XP Pro; Excel 2003

Here's what I'm trying to do.

if A1:A10 and left (B1:B10,2) = D1 & E1 then sum C1:C10

Here's my formula = SUMIF(A1:A10 & LEFT(B1:B10,2),D1&E1,C1:C10)

Can't get it to take, either as a formula or as an array.

I may need to use sumproduct. I would need help with sumproduct as I'm still learning to use sumproduct.

I would rather not add any more helper columns, but I know I can concatenate this data in the helper columns and then use a simple sumif formula with a single condition for the first argument.

Any thoughts?

THANK YOU.

#### Jonmo1

##### MrExcel MVP
what is the &"#" for ?

##### Active Member

I think this not only works, but it may allow me to remove 2 helper rows already inserted.

So I figure I can use Genius for Jonmo and Brilliant for Aladin as Genious and Jonmo are shorter than Brilliant and Aladin Akyurek.

Does that work for you?

THANK YOU!!!

(former log-on of jxderg77)

##### MrExcel MVP
what is the &"#" for ?

C2:

=A2&B2

will flounder on...

A2: 1
B2: 11

A3: 11
B3: 1

Hence # as in-between separator.

I see, thanks...

Replies
9
Views
229
Replies
2
Views
156
Replies
1
Views
287
Replies
1
Views
239
Replies
19
Views
362

1,191,420
Messages
5,986,470
Members
440,031
Latest member
davidvillegasr

### 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