sumif statment

[Rashid]

Hi to All.

How would i write a sumif statment using the first character (left
function) of WH range to pull the data into the Final Values

original LIST
WH VALUE
A4 15,000
A6 6,500
B4 -313,079
BZ 20,000
C4 -335,343
D4 -393,476
H3 500,000
H4 3,200,000

FINAL VALUES - sumif
WH VALUE
A 21,500
B -293,097
C -335,343
D -393,476
H 3,700,000


Thanks,

Rashid

 

[Guest]

Hi Rashid

try following:
=SUM(IF(LEFT($A$3:$A$10;1)=A14;1;0)*$C$3:$C$10)

this is an array formula, do not accept this with enter but with
Ctrl + Shift + Enter, it should have {} around the formula afterwards.

hth

Carlo

 

[Bob Phillips]

=SUMPRODUCT(--(LEFT($A$2:$A$20,1)="A"),$B$2:$B$20)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Rashid]

Hi Carlo,
it doesnt work, Let me explain again... the first list has $$ value
assigned to warehouse #. (which is alphanumaric). What I am looking for
is the sum function to first convert the WH number to just an alphabet,
then sumIF based on alphabet reference to the the second list .


I have put the FINAL result that I am looking for in the second list...

WH Value Fac Value
A4 1000 A 1500
A4 500 B 700
B3 700 C 300
C2 200
C2 100

Thanks,

Rashid

 

[Bob Phillips]

=SUM(IF(LEFT(A2:A6)="A",B2:B6))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

although Carlo's formula, whilst a bit more unwieldy, achieves the same
results if you adjust the ranges to your data, and store A in A14. Maybe it
was the ; delimiters

=SUM(IF(LEFT($A$2:$A$10,1)=A14,1,0)*$B$2:$B$10)

again array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)