SUMIF

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 [email protected]> wrote in
message news:[email protected]...
> 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 <[email protected]>,
"john the confused" <john the [email protected]>
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 <[email protected]>,
> "john the confused" <john the [email protected]>
> 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
>

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 [email protected]> wrote in
message news:[email protected]...
> 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 <[email protected]>,
"john the confused" <john the [email protected]>
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 <[email protected]>,
> "john the confused" <john the [email protected]>
> 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
>

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 [email protected]> wrote in
message news:[email protected]...
> 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 <[email protected]>,
"john the confused" <john the [email protected]>
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 <[email protected]>,
> "john the confused" <john the [email protected]>
> 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
>