SUMPRODUCT of two columns if third column matches condition

[PeregrinTook]

Hey all,

Having a mind-blank and can't get this formula to work - hope someone here can help please!

I need to look up a large table and get the sumproduct of (Cartons x KG/Carton) where 'Deleted'=No.

The following worked perfectly before I needed to introduce the conditional check on Deleted status:

=SUMPRODUCT(SupplySummary!$C$4:$C$504,SupplySummary!L$4:L$504)

Where Col C is KG/Carton & Col L is Cartons.

I have tried many combinations of adding in SupplySummary!$AT$4:$AT$504="No" but just can't get it to work.

Can someone please help me with the structure for this scenario?

Many thanks
PT

 

[Domenic]

Try...

=SUMPRODUCT(--(SupplySummary!$AT$4:$AT$504="No"),SupplySummary!$C$4:$C$504,SupplySummary!L$4:L$504)

 

[PeregrinTook]

Beautiful Domenic - many thanks!

Can you explain exactly what the '--' does before the conditional check please? Should this be the format for all conditional checks - ie:

sumproduct(--(check),--(check),--(check),colToSum1,colToSum2)?

Just wondering for future reference...

Cheers v much
PT

 

[Domenic]

Beautiful Domenic - many thanks!

You're very welcome!

Can you explain exactly what the '--' does before the conditional check please?

Have a look here...

http://www.mrexcel.com/forum/showthread.php?t=128907

Should this be the format for all conditional checks - ie:

sumproduct(--(check),--(check),--(check),colToSum1,colToSum2)?

Just wondering for future reference...

Yes...

 

[PeregrinTook]

Perfect, thanks again Domenic - much appreciated.