ExcelBanter - sumif statment

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - sumif statment (https://www.excelbanter.com/excel-programming/372861-sumif-statment.html)

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

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

"Rashid" wrote:

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

=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" wrote in message
ups.com...
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

sumif statment including left function

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

Carlo wrote:
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

"Rashid" wrote:

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

sumif statment including left function

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

"Rashid" wrote in message
oups.com...
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

Carlo wrote:
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

"Rashid" wrote:

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