Combination sumif() and isnumber()

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George - 22 Feb 2008 18:20 GMT

Hi to everyone.
Is it possible to combine somehow the functions SUMIF() and ISNUMBER() ?.
(For example, how can I say in Excel, at the end of a big column, filled of
miscellaneous data “Sum all the values that are numbers” ? )

Reply to this Message

Rick Rothstein (MVP - VB) - 22 Feb 2008 18:26 GMT

Doesn't the SUM function work directly for you? For example,

=SUM(A1:A1000)

Rick

> Hi to everyone.
> Is it possible to combine somehow the functions SUMIF() and ISNUMBER() ?.
> (For example, how can I say in Excel, at the end of a big column, filled
> of
> miscellaneous data “Sum all the values that are numbers” ? )

Reply to this Message

George - 22 Feb 2008 18:35 GMT

All the data arent numbers.

> Doesn't the SUM function work directly for you? For example,
>
[quoted text clipped - 7 lines]
> > of
> > miscellaneous data “Sum all the values that are numbers” ? )

Reply to this Message

Rick Rothstein (MVP - VB) - 22 Feb 2008 18:37 GMT

When I try SUM on a column of numbers and non-numbers, I get the total of
the numbers... doesn't SUM work the same for you?

Rick

> All the data arent numbers.
>
[quoted text clipped - 11 lines]
>> > of
>> > miscellaneous data “Sum all the values that are numbers” ? )

Reply to this Message

George - 22 Feb 2008 18:37 GMT

Some cells include error msg (i use vlookup()) and the simple SUM dont work

> Doesn't the SUM function work directly for you? For example,
>
[quoted text clipped - 7 lines]
> > of
> > miscellaneous data “Sum all the values that are numbers” ? )

Reply to this Message

Rick Rothstein (MVP - VB) - 22 Feb 2008 18:51 GMT

Ah, information you neglected to mention in your first two messages.<g>

Try this array-entered** formula...

=SUM(IF(ISERROR(A1:A8),0,A1:A8))

** Commit the formula by pressing Ctrl+Shift+Enter instead of just Enter

Rick

> Some cells include error msg (i use vlookup()) and the simple SUM dont
> work
[quoted text clipped - 12 lines]
>> > of
>> > miscellaneous data “Sum all the values that are numbers” ? )

Reply to this Message

George - 22 Feb 2008 18:58 GMT

Thanks. I try it.
(But just to know, in general, is it possible to combine the two functions ?)

> Ah, information you neglected to mention in your first two messages.<g>
>
[quoted text clipped - 22 lines]
> >> > of
> >> > miscellaneous data “Sum all the values that are numbers” ? )

Reply to this Message

T. Valko - 22 Feb 2008 20:03 GMT

Try this:

=SUMIF(A:A,"<"&1E100)

Or:

=SUMIF(A:A,"<1E100")

Signature

Biff
Microsoft Excel MVP

> Thanks. I try it.
> (But just to know, in general, is it possible to combine the two functions
[quoted text clipped - 27 lines]
>> >> > of
>> >> > miscellaneous data "Sum all the values that are numbers" ? )

Reply to this Message

Harlan Grove - 23 Feb 2008 06:26 GMT

George <Geo...@discussions.microsoft.com> wrote...
...

>(But just to know, in general, is it possible to combine the two
>functions ?)

No, not possible.

Reply to this Message

scott - 22 Feb 2008 19:28 GMT

Normally the math works if there is text in the column.
Errors in the column seem to cause math calculation problems though.
Do you have errors in the column you arre calculating?
Scott

>Hi to everyone.
>Is it possible to combine somehow the functions SUMIF() and ISNUMBER() ?.
>(For example, how can I say in Excel, at the end of a big column, filled of
>miscellaneous data Sum all the values that are numbers ? )

Reply to this Message

George - 23 Feb 2008 11:33 GMT

Sometimes yes. I use vlookup() and sometimes there are some blank cells in
the list. In this case the result is an error msg in the column and cant sum
the column.

> Normally the math works if there is text in the column.
> Errors in the column seem to cause math calculation problems though.
[quoted text clipped - 5 lines]
> >(For example, how can I say in Excel, at the end of a big column, filled of
> >miscellaneous data “Sum all the values that are numbers” ? )

Reply to this Message