Sumifs in Excel 2007 for only numerical values

[Tigerxxx]

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

 

[Mike H]

Hi,

I would use Sumproduct

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

Mike

 

[T. Valko]

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)

 

[Tigerxxx]

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]

Maybe this:

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

 

[Tigerxxx]

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]

I have alphabets in the series too; hence need to check the

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.

 

[T. Valko]

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.

 

[Tigerxxx]

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]

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)

 

[Tigerxxx]

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]

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.

 

[Tigerxxx]

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]

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.

 

[Tigerxxx]

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]

=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),D110)

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