=SUMIF Help

[Padawan]

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]

Does this work...

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

 

[Aladin Akyurek]

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

=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]

Hey Aladin,
what is the &"#" for ?

 

[Padawan]

Jonmo & Aladin

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)

 

[Aladin Akyurek]

Hey Aladin,
what is the &"#" for ?

C2:

=A2&B2

will flounder on...

A2: 1
B2: 11

A3: 11
B3: 1

Hence # as in-between separator.

 

[Jonmo1]

I see, thanks...