multiple conditions for sum if function

  • Thread starter Thread starter Dave W
  • Start date Start date
D

Dave W

I am using this formula in a worksheet

=SUMIF(B7:B60,"<>L.08_InstallationLabor",K7:K60)

I would like to add multiple conditions

ex: =SUMIF(B7:B60,("<>L.08_InstallationLabor"),
("<>L.09_InstallationSub"),K7:K60))

I know this isn't right, but I need some help

I have looked a number of places to for the proper syntax

Can anyone help or point me in the right direction

Thanks in advance
 
Hi Dave

you need to use sumproduct

try
=SUMPRODUCT((B7:B60<>"L.08_InstallationLabor")*(B7:B60<>"L.09_InstallationSu
b")*(K7:K60))

Cheers
JulieD
 
Hi

You could use two SUMIFs and add them together:
=SUMIF(B7:B60,"<>L.08_InstallationLabor",K7:K60)+SUMIF(B7:B60,"<>L.09_Instal
lationSub"),K7:K60))
 
That formula is returning a #value error
no other cell in my worksheet shows an error
I,ve double checked the names everthing seems fine???

the formula is acting like it doesn't know which
expression to use?

any thoughts
 
Hi Dave

this was my test string
=SUMPRODUCT((B1:B20<>"cat")*(B1:B20<>"dog")*(A1:A20))
can you spot any differences in the syntax of this and what you're using?

if not, could you copy & paste your actual formula into your next post

Cheers
JulieD
 
Dave, it is all on one line in the cell isn't it?

failing that if you want to zip your workbook up & email direct to me i'll
take a look

cheers
JulieD
 
Anonymous,

Sorry to stick my nose into an ongoing discussion, but would you care to
explain the difference between the formula you offer and the formula that
Julie offered originally (apart from layout)? Following "This is the actual
formula" with the formula that she gave in the first place seems slightly
bizarre to me.

Dave,

There's absolutely nothing wrong with the formula as given (except for
getting carried away with *s and ()s - sorry Julie, couldn't resist)

=SUMPRODUCT((B7:B60<>"L.08_InstallationLabor")*
(B7:B60<>"L.09_InstallationSub"),K7:K60)

it should work for the situation that you described.


Steve D.
 
Back
Top