Hello,

How can I write a "Sumifs" formula in Excel 2007 which sums only if the
range of a criteria contains numbers?
Example: if sum cells b2:b8 only if cells a2:a8 contain numbers.

Thank you

Hi,

I would use Sumproduct

=SUMPRODUCT((ISNUMBER(A1:A8))*(B1:B8))

Mike

"Tigerxxx" wrote:

Hello,

How can I write a "Sumifs" formula in Excel 2007 which sums only if the
range of a criteria contains numbers?
Example: if sum cells b2:b8 only if cells a2:a8 contain numbers.

Thank you

If the numbers in A2:A8 are always positive numbers then you can use this:

=SUMIF(A2:A8,"=0",B2:B8)

If there might be negative numbers in A2:A8 then try this:

=SUMIF(A2:A8,"<1E100",B2:B8)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hello,

How can I write a "Sumifs" formula in Excel 2007 which sums only if the
range of a criteria contains numbers?
Example: if sum cells b2:b8 only if cells a2:a8 contain numbers.

Thank you

Actually I have alphabets in the series too; hence need to check the
condition specifically for "non-numerical" numbers.
Any ideas on how to write a condition for "non-numerical" numbers in a
"sumif" formula?

"T. Valko" wrote:

If the numbers in A2:A8 are always positive numbers then you can use this:

=SUMIF(A2:A8,"=0",B2:B8)

If there might be negative numbers in A2:A8 then try this:

=SUMIF(A2:A8,"<1E100",B2:B8)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hello,

How can I write a "Sumifs" formula in Excel 2007 which sums only if the
range of a criteria contains numbers?
Example: if sum cells b2:b8 only if cells a2:a8 contain numbers.

Thank you

Maybe this:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),B1:B10)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Actually I have alphabets in the series too; hence need to check the
condition specifically for "non-numerical" numbers.
Any ideas on how to write a condition for "non-numerical" numbers in a
"sumif" formula?

"T. Valko" wrote:

If the numbers in A2:A8 are always positive numbers then you can use
this:

=SUMIF(A2:A8,"=0",B2:B8)

If there might be negative numbers in A2:A8 then try this:

=SUMIF(A2:A8,"<1E100",B2:B8)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hello,

How can I write a "Sumifs" formula in Excel 2007 which sums only if the
range of a criteria contains numbers?
Example: if sum cells b2:b8 only if cells a2:a8 contain numbers.

Thank you

Thank you for the responses.
Actually a syntax for "sumifs" formula would really help as there are other
conditions I am checking too in the "sumifs" formula which I cannot with the
"sumproduct" formula.

"T. Valko" wrote:

Maybe this:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),B1:B10)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Actually I have alphabets in the series too; hence need to check the
condition specifically for "non-numerical" numbers.
Any ideas on how to write a condition for "non-numerical" numbers in a
"sumif" formula?

"T. Valko" wrote:

If the numbers in A2:A8 are always positive numbers then you can use
this:

=SUMIF(A2:A8,"=0",B2:B8)

If there might be negative numbers in A2:A8 then try this:

=SUMIF(A2:A8,"<1E100",B2:B8)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hello,

How can I write a "Sumifs" formula in Excel 2007 which sums only if the
range of a criteria contains numbers?
Example: if sum cells b2:b8 only if cells a2:a8 contain numbers.

Thank you

I have alphabets in the series too; hence need to check the
condition specifically for "non-numerical" numbers.
there are other conditions I am checking too

OK, I think it's time for you to post a small sample of your data and
explain what you're wanting to do. Also, explain what "non-numerical"
numbers are.

SUMIFS is limited to "straight comparisons" only. You can't manipulate an
array for a condition. For example, A1:A10 hold dates that span several
years, B1:B10 hold sales numbers. You want to sum the sales numbers for a
certain month of any year. You can't use SUMIFS for something like that.


--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thank you for the responses.
Actually a syntax for "sumifs" formula would really help as there are
other
conditions I am checking too in the "sumifs" formula which I cannot with
the
"sumproduct" formula.

"T. Valko" wrote:

Maybe this:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),B1:B10)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Actually I have alphabets in the series too; hence need to check the
condition specifically for "non-numerical" numbers.
Any ideas on how to write a condition for "non-numerical" numbers in a
"sumif" formula?

"T. Valko" wrote:

If the numbers in A2:A8 are always positive numbers then you can use
this:

=SUMIF(A2:A8,"=0",B2:B8)

If there might be negative numbers in A2:A8 then try this:

=SUMIF(A2:A8,"<1E100",B2:B8)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hello,

How can I write a "Sumifs" formula in Excel 2007 which sums only if
the
range of a criteria contains numbers?
Example: if sum cells b2:b8 only if cells a2:a8 contain numbers.

Thank you