CSE function with #VALUE errors
 

Hi,
In a column of a spreadsheet I use at work is data for the number of
transactions of different types, in a precise format (2 buy, 3 sell, 5
sell, etc). I have to total the transactions of different types, and I
thought I could get excel to do it for me. More than that, I thought a
CSE could do it in one cell, but the function seems to have become a bit
of a monster. This is my tester for it;

=SUM(IF(EXACT(RIGHT(A1:A3,LEN(A1:A3)-FIND(" ",A1:A3)),"buy"),
VALUE(LEFT(A1:A3,FIND(" ",A1:A3)-1)),0))

it sums only the numbers in cells that end in "buy". It works a charm, but
it can't handle any blank cells in the range, which makes it all but
useless for my purposes. I tried experimenting with ISERROR, but without
much luck. This is my best attempt so far;

=SUM(IF(EXACT(IF(ISERROR(FIND(" ",A1:A3)),0,
RIGHT(A1:A3,LEN(A1:A3)-FIND(" ",A1:A3))),"buy"),
IF(ISERROR(FIND(" ",A1:A3)),0,VALUE(LEFT(A1:A3,FIND(" ",A1:A3)-1))),0))

Now, if the first cell in the range is empty it still gives #VALUE, but
any other blank cell just ruins the sum and it gives 0. That's the best I
can do.

I realise now that maybe solving it inline is probably not the best way to
do it, and that I'm betraying all the laws of readability, but I'm
determined to make it work now, and I'd really like some help. Am I
barking up the wrong tree with ISERROR? Is there a better function you
know of that can help? What do you think?

thanks
P

CSE function with #VALUE errors
 

Try this array formula (CSE):

All on one line.

=SUM(IF(ISNUMBER(SEARCH("buy",A1:A10)),
--LEFT(A1:A10,FIND(" ",A1:A10)-1)))

--
Biff
Microsoft Excel MVP


"Phillip" wrote in message
...
Hi,
In a column of a spreadsheet I use at work is data for the number of
transactions of different types, in a precise format (2 buy, 3 sell, 5
sell, etc). I have to total the transactions of different types, and I
thought I could get excel to do it for me. More than that, I thought a
CSE could do it in one cell, but the function seems to have become a bit
of a monster. This is my tester for it;

=SUM(IF(EXACT(RIGHT(A1:A3,LEN(A1:A3)-FIND(" ",A1:A3)),"buy"),
VALUE(LEFT(A1:A3,FIND(" ",A1:A3)-1)),0))

it sums only the numbers in cells that end in "buy". It works a charm, but
it can't handle any blank cells in the range, which makes it all but
useless for my purposes. I tried experimenting with ISERROR, but without
much luck. This is my best attempt so far;

=SUM(IF(EXACT(IF(ISERROR(FIND(" ",A1:A3)),0,
RIGHT(A1:A3,LEN(A1:A3)-FIND(" ",A1:A3))),"buy"),
IF(ISERROR(FIND(" ",A1:A3)),0,VALUE(LEFT(A1:A3,FIND(" ",A1:A3)-1))),0))

Now, if the first cell in the range is empty it still gives #VALUE, but
any other blank cell just ruins the sum and it gives 0. That's the best I
can do.

I realise now that maybe solving it inline is probably not the best way to
do it, and that I'm betraying all the laws of readability, but I'm
determined to make it work now, and I'd really like some help. Am I
barking up the wrong tree with ISERROR? Is there a better function you
know of that can help? What do you think?

thanks
P

CSE function with #VALUE errors
 

On Thu, 04 Oct 2007 18:12:46 -0400, T. Valko wrote:

Try this array formula (CSE):

All on one line.

=SUM(IF(ISNUMBER(SEARCH("buy",A1:A10)),
--LEFT(A1:A10,FIND(" ",A1:A10)-1)))



Thanks. That'll make life a lot easier

P

CSE function with #VALUE errors
 

"Phillip" wrote in message
...
On Thu, 04 Oct 2007 18:12:46 -0400, T. Valko wrote:

Try this array formula (CSE):

All on one line.

=SUM(IF(ISNUMBER(SEARCH("buy",A1:A10)),
--LEFT(A1:A10,FIND(" ",A1:A10)-1)))



Thanks. That'll make life a lot easier

P

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP