Hi again,
How do you pull the numbers in a column to compare against something using
the sumproduct formula when there may be letters in the column. i have a
column that has letters and numbers. I don't want to manipulate the column
or make new ones on the side. What i'm looking to do is to count all the
numbers that are less than say 5 while ignoring the letters. There would be
some other criteria as well (in another column) but that doesn't matter for
this question.

Ex.

header
1
2
4
6
7

Answer = 2

I've tried using sumproduct(--(isnumber(A:A)5) but it gives a value b/c of
the "Header" text and also it overwrites the actual values.

Thanks again,
Josh

Try

=COUNTIF(A:A,"5")

If this post helps click Yes
---------------
Jacob Skaria


"jxbeeman" wrote:

Hi again,
How do you pull the numbers in a column to compare against something using
the sumproduct formula when there may be letters in the column. i have a
column that has letters and numbers. I don't want to manipulate the column
or make new ones on the side. What i'm looking to do is to count all the
numbers that are less than say 5 while ignoring the letters. There would be
some other criteria as well (in another column) but that doesn't matter for
this question.

Ex.

header
1
2
4
6
7

Answer = 2

I've tried using sumproduct(--(isnumber(A:A)5) but it gives a value b/c of
the "Header" text and also it overwrites the actual values.

Thanks again,
Josh

Jacob,
I need to have mulitple criteria, that's why i can't use the countif
function. I need to use mulitiple columns to run this formula with different
criteria which this scenario is only one of those columns. I have the logic
for the other columns working except for this one which has stumped me.

Thanks,
Josh

"Jacob Skaria" wrote:

Try

=COUNTIF(A:A,"5")

If this post helps click Yes
---------------
Jacob Skaria


"jxbeeman" wrote:

Hi again,
How do you pull the numbers in a column to compare against something using
the sumproduct formula when there may be letters in the column. i have a
column that has letters and numbers. I don't want to manipulate the column
or make new ones on the side. What i'm looking to do is to count all the
numbers that are less than say 5 while ignoring the letters. There would be
some other criteria as well (in another column) but that doesn't matter for
this question.

Ex.

header
1
2
4
6
7

Answer = 2

I've tried using sumproduct(--(isnumber(A:A)5) but it gives a value b/c of
the "Header" text and also it overwrites the actual values.

Thanks again,
Josh

Example of SUMPRODUCT() to count based on 3 conditions..

=SUMPRODUCT(($A$1:$A$100=D2)*($B$1:$B$1005)*($C$1 :$C$100=D4))

If this post helps click Yes
---------------
Jacob Skaria


"jxbeeman" wrote:

Jacob,
I need to have mulitple criteria, that's why i can't use the countif
function. I need to use mulitiple columns to run this formula with different
criteria which this scenario is only one of those columns. I have the logic
for the other columns working except for this one which has stumped me.

Thanks,
Josh

"Jacob Skaria" wrote:

Try

=COUNTIF(A:A,"5")

If this post helps click Yes
---------------
Jacob Skaria


"jxbeeman" wrote:

Hi again,
How do you pull the numbers in a column to compare against something using
the sumproduct formula when there may be letters in the column. i have a
column that has letters and numbers. I don't want to manipulate the column
or make new ones on the side. What i'm looking to do is to count all the
numbers that are less than say 5 while ignoring the letters. There would be
some other criteria as well (in another column) but that doesn't matter for
this question.

Ex.

header
1
2
4
6
7

Answer = 2

I've tried using sumproduct(--(isnumber(A:A)5) but it gives a value b/c of
the "Header" text and also it overwrites the actual values.

Thanks again,
Josh

This goes in a cell that's not in the range you're inspecting:
=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(A1:A105))

(I didn't understand your last comment.)

And you can only use the entire column in xl2007.

jxbeeman wrote:

Hi again,
How do you pull the numbers in a column to compare against something using
the sumproduct formula when there may be letters in the column. i have a
column that has letters and numbers. I don't want to manipulate the column
or make new ones on the side. What i'm looking to do is to count all the
numbers that are less than say 5 while ignoring the letters. There would be
some other criteria as well (in another column) but that doesn't matter for
this question.

Ex.

header
1
2
4
6
7

Answer = 2

I've tried using sumproduct(--(isnumber(A:A)5) but it gives a value b/c of
the "Header" text and also it overwrites the actual values.

Thanks again,
Josh

--

Dave Peterson

Thanks Dave,
I still have a problem with a #VALUE!
if you use the formula in the following,
SUMPRODUCT(--((RIGHT(A2:A8,2)*1)5),--(ISNUMBER((RIGHT(A2:A8,2)*1))))
The result of one of the rows in the first Array has a VALUE!.
VALUE! * (ANYTHING) = VALUE1.
So i guess the question is how do i change this Value to a 0 without
stomping on the original data

Ex.
A
1 #VALUE!
1
text
2
2
6
6
Text

Array should look like this
0
0
0
0
0
1
1
0

but looks like this
0
0
VALUE!
0
0
1
1
VALUE!

Any ideas?
Thanks again,
Josh


"Dave Peterson" wrote:

This goes in a cell that's not in the range you're inspecting:
=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(A1:A105))

(I didn't understand your last comment.)

And you can only use the entire column in xl2007.

jxbeeman wrote:

Hi again,
How do you pull the numbers in a column to compare against something using
the sumproduct formula when there may be letters in the column. i have a
column that has letters and numbers. I don't want to manipulate the column
or make new ones on the side. What i'm looking to do is to count all the
numbers that are less than say 5 while ignoring the letters. There would be
some other criteria as well (in another column) but that doesn't matter for
this question.

Ex.

header
1
2
4
6
7

Answer = 2

I've tried using sumproduct(--(isnumber(A:A)5) but it gives a value b/c of
the "Header" text and also it overwrites the actual values.

Thanks again,
Josh

--

Dave Peterson

You could drop the =sumproduct() formula and use an =sum(if(..)) array formula:

=sum(if(isnumber(a1:a10),if(a1:a105,1)))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

jxbeeman wrote:

Thanks Dave,
I still have a problem with a #VALUE!
if you use the formula in the following,
SUMPRODUCT(--((RIGHT(A2:A8,2)*1)5),--(ISNUMBER((RIGHT(A2:A8,2)*1))))
The result of one of the rows in the first Array has a VALUE!.
VALUE! * (ANYTHING) = VALUE1.
So i guess the question is how do i change this Value to a 0 without
stomping on the original data

Ex.
A
1 #VALUE!
1
text
2
2
6
6
Text

Array should look like this
0
0
0
0
0
1
1
0

but looks like this
0
0
VALUE!
0
0
1
1
VALUE!

Any ideas?
Thanks again,
Josh

"Dave Peterson" wrote:

This goes in a cell that's not in the range you're inspecting:
=SUMPRODUCT(--(ISNUMBER(A1:A10)),--(A1:A105))

(I didn't understand your last comment.)

And you can only use the entire column in xl2007.

jxbeeman wrote:

Hi again,
How do you pull the numbers in a column to compare against something using
the sumproduct formula when there may be letters in the column. i have a
column that has letters and numbers. I don't want to manipulate the column
or make new ones on the side. What i'm looking to do is to count all the
numbers that are less than say 5 while ignoring the letters. There would be
some other criteria as well (in another column) but that doesn't matter for
this question.

Ex.

header
1
2
4
6
7

Answer = 2

I've tried using sumproduct(--(isnumber(A:A)5) but it gives a value b/c of
the "Header" text and also it overwrites the actual values.

Thanks again,
Josh

--

Dave Peterson


--

Dave Peterson