Sumproduct Formula

[ccameron]

Hi Guys,
I am trying to use a sumproduct formula to calculate using multiple criteria, the formula I have been using was working until I had to add another criteria. Not sure what I have done wrong could someone please point me in the right direction.

This works

=SUMPRODUCT(--('Sealed Roads'!$Q$14:$Q$200=1),--('Sealed Roads'!$B$14:$B$200="Mildura.CBD"),'Sealed Roads'!$R$14:$R$200)

This does not? I need to subract any value in $R$14:$R$200 if $AA$14:$AA$200 = completed

=SUMPRODUCT(--('Sealed Roads'!$Q$14:$Q$200=1),--('Sealed Roads'!$B$14:$B$200="Mildura.CBD"),'Sealed Roads'!$R$14:$R$200)-('Sealed Roads'!$AA$14:$AA$200="completed")

 

[Marcelo Branco]

Maybe this

=SUMPRODUCT(--('Sealed Roads'!$Q$14:$Q$200=1),--('Sealed Roads'!$B$14:$B$200="Mildura.CBD"),--('Sealed Roads'!$AA$14:$AA$200<>"completed"), 'Sealed Roads'!$R$14:$R$200)

M.

 

[ccameron]

Thanks Marcelo,
I will give it a try when i get to work.
Thanks again
ccameron

 

[Marcelo Branco]

Thanks Marcelo,
I will give it a try when i get to work.
Thanks again
ccameron

You are welcome!

Hope it helps

M.