Sumifs in Excel 2007 for only numerical values
 

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

Sumifs in Excel 2007 for only numerical values
 

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

Sumifs in Excel 2007 for only numerical values
 

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

Sumifs in Excel 2007 for only numerical values
 

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

Sumifs in Excel 2007 for only numerical values
 

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

Sumifs in Excel 2007 for only numerical values
 

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

Sumifs in Excel 2007 for only numerical values
 

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

Sumifs in Excel 2007 for only numerical values
 

P.S.

there are other conditions I am checking too in the
"sumifs" formula which I cannot with the "sumproduct" formula.

Actually, SUMPRODUCT can do everything that SUMIFS can do. But, SUMIFS can't
do everything that SUMPRODUCT can do.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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

Sumifs in Excel 2007 for only numerical values
 

Hi,

I am sorry but I never realized there was a response to my query.
Example: My data range reads something as follows in row 1:
5, 7, 2, 3, x, 5, x, 1,...etc.
I also have a different set of numerical data in row 2.
I want to sum row 2 only if the corresponding cell in row 1 is a number.

Hope I was able to explain correctly. Thank you.

"T. Valko" wrote:

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

Sumifs in Excel 2007 for only numerical values
 

Ok, you can se a SUMIF formula since there's a single condition, if row 1 is
a number.

If the numbers will *always* be positive numbers:

=SUMIF(A1:J1,"=0",A2:J2)

If there can be both positive and negative numbers:

=SUMIF(A1:J1,"<1E100",A2:J2)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

I am sorry but I never realized there was a response to my query.
Example: My data range reads something as follows in row 1:
5, 7, 2, 3, x, 5, x, 1,...etc.
I also have a different set of numerical data in row 2.
I want to sum row 2 only if the corresponding cell in row 1 is a number.

Hope I was able to explain correctly. Thank you.

"T. Valko" wrote:

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

Sumifs in Excel 2007 for only numerical values
 

Thanks a lot.
If I were to use the Sumproduct formula, and I had the following data:

a1:a10 is the data with numbers and non-numbers
b1:b10 is the year numbers i.e. 2009, 2008 etc.
c1 is a cell which contains a year number to be summed for.

If I wanted to add the data in a1:a10 which are only numbers and the
corresponding cells in b1:b10 represent a year which is equal to the value of
a year in cell C1; I would write the formula as:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),a1:a10)

However the above syntax does not seem to work.
Can you please help correct?

"T. Valko" wrote:

Ok, you can se a SUMIF formula since there's a single condition, if row 1 is
a number.

If the numbers will *always* be positive numbers:

=SUMIF(A1:J1,"=0",A2:J2)

If there can be both positive and negative numbers:

=SUMIF(A1:J1,"<1E100",A2:J2)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

I am sorry but I never realized there was a response to my query.
Example: My data range reads something as follows in row 1:
5, 7, 2, 3, x, 5, x, 1,...etc.
I also have a different set of numerical data in row 2.
I want to sum row 2 only if the corresponding cell in row 1 is a number.

Hope I was able to explain correctly. Thank you.

"T. Valko" wrote:

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

Sumifs in Excel 2007 for only numerical values
 

SUMIF will work in this application:

=SUMIF(B1:B10,C1,A1:A10)

If that doesn't work then you'll need to provide some sample data. I'm kind
of "nervous" about the term "non-numbers". Non-numbers can include TEXT
numbers, numbers formatted as TEXT. Some functions will ignore text numbers
and some won't. For example:

A1 = 10 (a real numeric number)
A2 = 10 (a TEXT number)

If you have not changed the default cell alignment it's easy to spot the
difference:

...........10
10.........

By default numeric numbers *always* align to the right of a cell.

By default TEXT (including TEXT numbers) *always* aligns to the left of the
cell.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks a lot.
If I were to use the Sumproduct formula, and I had the following data:

a1:a10 is the data with numbers and non-numbers
b1:b10 is the year numbers i.e. 2009, 2008 etc.
c1 is a cell which contains a year number to be summed for.

If I wanted to add the data in a1:a10 which are only numbers and the
corresponding cells in b1:b10 represent a year which is equal to the value
of
a year in cell C1; I would write the formula as:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),a1:a10)

However the above syntax does not seem to work.
Can you please help correct?

"T. Valko" wrote:

Ok, you can se a SUMIF formula since there's a single condition, if row 1
is
a number.

If the numbers will *always* be positive numbers:

=SUMIF(A1:J1,"=0",A2:J2)

If there can be both positive and negative numbers:

=SUMIF(A1:J1,"<1E100",A2:J2)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

I am sorry but I never realized there was a response to my query.
Example: My data range reads something as follows in row 1:
5, 7, 2, 3, x, 5, x, 1,...etc.
I also have a different set of numerical data in row 2.
I want to sum row 2 only if the corresponding cell in row 1 is a
number.

Hope I was able to explain correctly. Thank you.

"T. Valko" wrote:

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

Sumifs in Excel 2007 for only numerical values
 

Hi,

The sumifs formula works. However now I was curious to see how it could be
written in the sumproduct formula since the consensus seems to be that
sunproduct formula is better than sumifs formula.
Non-numbers that I have is simply a character "x" i.e. a text character.

Hence, could you help explain how I could write this using a sumproduct
formula with the conditions i mentioned in my previous post.

Thanks again!

"T. Valko" wrote:

SUMIF will work in this application:

=SUMIF(B1:B10,C1,A1:A10)

If that doesn't work then you'll need to provide some sample data. I'm kind
of "nervous" about the term "non-numbers". Non-numbers can include TEXT
numbers, numbers formatted as TEXT. Some functions will ignore text numbers
and some won't. For example:

A1 = 10 (a real numeric number)
A2 = 10 (a TEXT number)

If you have not changed the default cell alignment it's easy to spot the
difference:

...........10
10.........

By default numeric numbers *always* align to the right of a cell.

By default TEXT (including TEXT numbers) *always* aligns to the left of the
cell.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks a lot.
If I were to use the Sumproduct formula, and I had the following data:

a1:a10 is the data with numbers and non-numbers
b1:b10 is the year numbers i.e. 2009, 2008 etc.
c1 is a cell which contains a year number to be summed for.

If I wanted to add the data in a1:a10 which are only numbers and the
corresponding cells in b1:b10 represent a year which is equal to the value
of
a year in cell C1; I would write the formula as:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),a1:a10)

However the above syntax does not seem to work.
Can you please help correct?

"T. Valko" wrote:

Ok, you can se a SUMIF formula since there's a single condition, if row 1
is
a number.

If the numbers will *always* be positive numbers:

=SUMIF(A1:J1,"=0",A2:J2)

If there can be both positive and negative numbers:

=SUMIF(A1:J1,"<1E100",A2:J2)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

I am sorry but I never realized there was a response to my query.
Example: My data range reads something as follows in row 1:
5, 7, 2, 3, x, 5, x, 1,...etc.
I also have a different set of numerical data in row 2.
I want to sum row 2 only if the corresponding cell in row 1 is a
number.

Hope I was able to explain correctly. Thank you.

"T. Valko" wrote:

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

Sumifs in Excel 2007 for only numerical values
 

This should do it:

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

the consensus seems to be that sunproduct formula
is better than sumifs formula.

Not necessarily. For simple straight comparisons with multiple conditions
SUMIFS is a better choice. For example:

Sum col D if col A=x and col B=y and col C=z.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

The sumifs formula works. However now I was curious to see how it could be
written in the sumproduct formula since the consensus seems to be that
sunproduct formula is better than sumifs formula.
Non-numbers that I have is simply a character "x" i.e. a text character.

Hence, could you help explain how I could write this using a sumproduct
formula with the conditions i mentioned in my previous post.

Thanks again!

"T. Valko" wrote:

SUMIF will work in this application:

=SUMIF(B1:B10,C1,A1:A10)

If that doesn't work then you'll need to provide some sample data. I'm
kind
of "nervous" about the term "non-numbers". Non-numbers can include TEXT
numbers, numbers formatted as TEXT. Some functions will ignore text
numbers
and some won't. For example:

A1 = 10 (a real numeric number)
A2 = 10 (a TEXT number)

If you have not changed the default cell alignment it's easy to spot the
difference:

...........10
10.........

By default numeric numbers *always* align to the right of a cell.

By default TEXT (including TEXT numbers) *always* aligns to the left of
the
cell.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks a lot.
If I were to use the Sumproduct formula, and I had the following data:

a1:a10 is the data with numbers and non-numbers
b1:b10 is the year numbers i.e. 2009, 2008 etc.
c1 is a cell which contains a year number to be summed for.

If I wanted to add the data in a1:a10 which are only numbers and the
corresponding cells in b1:b10 represent a year which is equal to the
value
of
a year in cell C1; I would write the formula as:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),a1:a10)

However the above syntax does not seem to work.
Can you please help correct?

"T. Valko" wrote:

Ok, you can se a SUMIF formula since there's a single condition, if
row 1
is
a number.

If the numbers will *always* be positive numbers:

=SUMIF(A1:J1,"=0",A2:J2)

If there can be both positive and negative numbers:

=SUMIF(A1:J1,"<1E100",A2:J2)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

I am sorry but I never realized there was a response to my query.
Example: My data range reads something as follows in row 1:
5, 7, 2, 3, x, 5, x, 1,...etc.
I also have a different set of numerical data in row 2.
I want to sum row 2 only if the corresponding cell in row 1 is a
number.

Hope I was able to explain correctly. Thank you.

"T. Valko" wrote:

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

Sumifs in Excel 2007 for only numerical values
 

Thanks a bunch! This helps!

a1:a10 is the data with numbers and non-numbers
b1:b10 is the year numbers i.e. 2009, 2008 etc.
d1:d10 are all numbers
c1 is a cell which contains a year number to be summed for.

I want to sum d1:d10 if the corresponding value in:
a1:a10 is a number i.e. it should not be a non-number.
B1:B10=C1...using the sumproduct formula.

The syntax I used is:
=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),d1:d10)
However this does not seem to be working


"T. Valko" wrote:

This should do it:

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

the consensus seems to be that sunproduct formula
is better than sumifs formula.

Not necessarily. For simple straight comparisons with multiple conditions
SUMIFS is a better choice. For example:

Sum col D if col A=x and col B=y and col C=z.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

The sumifs formula works. However now I was curious to see how it could be
written in the sumproduct formula since the consensus seems to be that
sunproduct formula is better than sumifs formula.
Non-numbers that I have is simply a character "x" i.e. a text character.

Hence, could you help explain how I could write this using a sumproduct
formula with the conditions i mentioned in my previous post.

Thanks again!

"T. Valko" wrote:

SUMIF will work in this application:

=SUMIF(B1:B10,C1,A1:A10)

If that doesn't work then you'll need to provide some sample data. I'm
kind
of "nervous" about the term "non-numbers". Non-numbers can include TEXT
numbers, numbers formatted as TEXT. Some functions will ignore text
numbers
and some won't. For example:

A1 = 10 (a real numeric number)
A2 = 10 (a TEXT number)

If you have not changed the default cell alignment it's easy to spot the
difference:

...........10
10.........

By default numeric numbers *always* align to the right of a cell.

By default TEXT (including TEXT numbers) *always* aligns to the left of
the
cell.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks a lot.
If I were to use the Sumproduct formula, and I had the following data:

a1:a10 is the data with numbers and non-numbers
b1:b10 is the year numbers i.e. 2009, 2008 etc.
c1 is a cell which contains a year number to be summed for.

If I wanted to add the data in a1:a10 which are only numbers and the
corresponding cells in b1:b10 represent a year which is equal to the
value
of
a year in cell C1; I would write the formula as:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),a1:a10)

However the above syntax does not seem to work.
Can you please help correct?

"T. Valko" wrote:

Ok, you can se a SUMIF formula since there's a single condition, if
row 1
is
a number.

If the numbers will *always* be positive numbers:

=SUMIF(A1:J1,"=0",A2:J2)

If there can be both positive and negative numbers:

=SUMIF(A1:J1,"<1E100",A2:J2)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

I am sorry but I never realized there was a response to my query.
Example: My data range reads something as follows in row 1:
5, 7, 2, 3, x, 5, x, 1,...etc.
I also have a different set of numerical data in row 2.
I want to sum row 2 only if the corresponding cell in row 1 is a
number.

Hope I was able to explain correctly. Thank you.

"T. Valko" wrote:

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

Sumifs in Excel 2007 for only numerical values
 

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),d1:d10)
However this does not seem to be working

That should work but you you don't need the double unary in front of A1:A10:

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

If that doesn't work then you'll have be more specific in describing what
"does not seem to be working" means.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks a bunch! This helps!

a1:a10 is the data with numbers and non-numbers
b1:b10 is the year numbers i.e. 2009, 2008 etc.
d1:d10 are all numbers
c1 is a cell which contains a year number to be summed for.

I want to sum d1:d10 if the corresponding value in:
a1:a10 is a number i.e. it should not be a non-number.
B1:B10=C1...using the sumproduct formula.

The syntax I used is:
=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),d1:d10)
However this does not seem to be working


"T. Valko" wrote:

This should do it:

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

the consensus seems to be that sunproduct formula
is better than sumifs formula.

Not necessarily. For simple straight comparisons with multiple conditions
SUMIFS is a better choice. For example:

Sum col D if col A=x and col B=y and col C=z.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

The sumifs formula works. However now I was curious to see how it could
be
written in the sumproduct formula since the consensus seems to be that
sunproduct formula is better than sumifs formula.
Non-numbers that I have is simply a character "x" i.e. a text
character.

Hence, could you help explain how I could write this using a sumproduct
formula with the conditions i mentioned in my previous post.

Thanks again!

"T. Valko" wrote:

SUMIF will work in this application:

=SUMIF(B1:B10,C1,A1:A10)

If that doesn't work then you'll need to provide some sample data. I'm
kind
of "nervous" about the term "non-numbers". Non-numbers can include
TEXT
numbers, numbers formatted as TEXT. Some functions will ignore text
numbers
and some won't. For example:

A1 = 10 (a real numeric number)
A2 = 10 (a TEXT number)

If you have not changed the default cell alignment it's easy to spot
the
difference:

...........10
10.........

By default numeric numbers *always* align to the right of a cell.

By default TEXT (including TEXT numbers) *always* aligns to the left
of
the
cell.

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Thanks a lot.
If I were to use the Sumproduct formula, and I had the following
data:

a1:a10 is the data with numbers and non-numbers
b1:b10 is the year numbers i.e. 2009, 2008 etc.
c1 is a cell which contains a year number to be summed for.

If I wanted to add the data in a1:a10 which are only numbers and the
corresponding cells in b1:b10 represent a year which is equal to the
value
of
a year in cell C1; I would write the formula as:

=SUMPRODUCT(--(ISNUMBER(--A1:A10)),--(b1:b10=c1),a1:a10)

However the above syntax does not seem to work.
Can you please help correct?

"T. Valko" wrote:

Ok, you can se a SUMIF formula since there's a single condition, if
row 1
is
a number.

If the numbers will *always* be positive numbers:

=SUMIF(A1:J1,"=0",A2:J2)

If there can be both positive and negative numbers:

=SUMIF(A1:J1,"<1E100",A2:J2)

--
Biff
Microsoft Excel MVP


"Tigerxxx" wrote in message
...
Hi,

I am sorry but I never realized there was a response to my query.
Example: My data range reads something as follows in row 1:
5, 7, 2, 3, x, 5, x, 1,...etc.
I also have a different set of numerical data in row 2.
I want to sum row 2 only if the corresponding cell in row 1 is a
number.

Hope I was able to explain correctly. Thank you.

"T. Valko" wrote:

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