sumif not equal problem

B

brianwa

Simple question. I'm trying to sum up sum data not equal to a certai
criteria. Below is the formula I'm using but it is not working. An
help would be appreciated.

=SUM(IF(Sheet2!A:A,"<>A9:A14",Sheet2!B:B)*(1-$B$5))

Thanks in advance
B
 
F

Frank Kabel

Hi
one way
=SUMRODUCT(--(Sheet2!A1:A1000<>A9),--(Sheet2!A1:A1000<>A10),--(Sheet2!A
1:A1000<>A11),--(Sheet2!A1:A1000<>A12),--(Sheet2!A1:A1000<>A13),--(Shee
t2!A1:A1000<>A14),Sheet2!B1:B1000)*(1-$B$5)
 
B

brianwa

Thanks Frank but this is not working (and I did notice the missing "p")
It won't let me enter the formula????

B
 
B

brianwa

With a "p" I got the following message:

"The forumula you typed contains and error"

For information about fixing common formula problems, click Help.

So forth

B
 
B

brianwa

I got it. Here's the solution

=SUMPRODUCT((Sheet2!A$2:A$1000<>A9)*(Sheet2!A$2:A$1000<>A10)*(Sheet2!A$2:A$1000<>A11)*(Sheet2!A$2:A$1000<>A12)*(Sheet2!A$2:A$1000<>A13)*(Sheet2!A$2:A$1000<>A14)*(Sheet2!B$2:B$1000))*(1-$B$5)

Frank, you were a great help as always!

Much appreciated.
B
 
Top