Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Dynamic sumif function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |