SUMIF
 

What I have is a list in column a of invoice numbers that either end in a
letter or a number & what I need to do is add up the invoice values from
column B for all the invoices that have a number at the end of them in column
A
ie
Cell A1 Invoice, B1 Amount
A2 "aaa111", B2 £100
A3 "bbb222", B3 £200
A4 "xxxxxx", B4 £150
A5 "zzzzzz", B5 £300
So I want to have the sum of B2 & B3 as these have invoicce numbers ending
in a number.

Any thoughts on doing this?

John

Hi

Here's a bit of a long solution - and I'm sure someone will post something
more elegant!
=SUMPRODUCT((NOT(ISERROR(VALUE(RIGHT(A2:A5,1)))))* (B2:B5))

Andy.

"john the confused" <john the wrote in
message ...
What I have is a list in column a of invoice numbers that either end in a
letter or a number & what I need to do is add up the invoice values from
column B for all the invoices that have a number at the end of them in
column
A
ie
Cell A1 Invoice, B1 Amount
A2 "aaa111", B2 £100
A3 "bbb222", B3 £200
A4 "xxxxxx", B4 £150
A5 "zzzzzz", B5 £300
So I want to have the sum of B2 & B3 as these have invoicce numbers ending
in a number.

Any thoughts on doing this?

John

Try...

=SUMPRODUCT(--(ISNUMBER(RIGHT(A2:A5)+0)),B2:B5)

Hope this helps!

In article ,
"john the confused" <john the
wrote:

What I have is a list in column a of invoice numbers that either end in a
letter or a number & what I need to do is add up the invoice values from
column B for all the invoices that have a number at the end of them in column
A
ie
Cell A1 Invoice, B1 Amount
A2 "aaa111", B2 £100
A3 "bbb222", B3 £200
A4 "xxxxxx", B4 £150
A5 "zzzzzz", B5 £300
So I want to have the sum of B2 & B3 as these have invoicce numbers ending
in a number.

Any thoughts on doing this?

John

They both work. Thans
John

"Domenic" wrote:

Try...

=SUMPRODUCT(--(ISNUMBER(RIGHT(A2:A5)+0)),B2:B5)

Hope this helps!

In article ,
"john the confused" <john the
wrote:

What I have is a list in column a of invoice numbers that either end in a
letter or a number & what I need to do is add up the invoice values from
column B for all the invoices that have a number at the end of them in column
A
ie
Cell A1 Invoice, B1 Amount
A2 "aaa111", B2 £100
A3 "bbb222", B3 £200
A4 "xxxxxx", B4 £150
A5 "zzzzzz", B5 £300
So I want to have the sum of B2 & B3 as these have invoicce numbers ending
in a number.

Any thoughts on doing this?

John