sumproduct multiplication

[jxbeeman]

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 Skaria]

Try

=COUNTIF(A:A,">5")

If this post helps click Yes

 

[jxbeeman]

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]

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

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

If this post helps click Yes

 

[Dave Peterson]

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

(I didn't understand your last comment.)

And you can only use the entire column in xl2007.

 

[jxbeeman]

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]

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

=sum(if(isnumber(a1:a10),if(a1:a10>5,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.)