Sehar has attended:
Excel VBA Introduction course
Excel Advanced - Formulas & Functions course
Sumproduct
Hello, I am using sumproduct to multiply the columns AB by M as below.
=SUMPRODUCT(AB39:AB193,M39:M193)
This works fine but what I want to do is then add a criteria, I want to multiply AB by M but I want excel to look at column B first, for example if B39 says 'Ramped' I want excel to multiply AB39 by M39 and continue, if not I want it to do nothing.
I've tried playing around with the formula but I can't get it to work, i've tried using the below but they either return an error or the incorrect amount, any ideas?
SUMPRODUCT((B39:B193="Ramped")*(AB39:AB193)*M39:M193)
SUMPRODUCT(--(B39:B193="Ramped"),M39:M193,AB39:AB193)
SUMPRODUCT(--(B39:B193="Ramped"),--(M39:M193,AB39:AB193))
Thank you
RE: Sumproduct
Hi Sehar,
Thank you for the forum question.
The formula below is correct. It will multiply all rows where column B= "Ramped" and then sum it up.
If the formula doesn't do it correct you must have another issue.
=SUMPRODUCT((B39:B193="Ramped")*(AB39:AB193)*M39:M193)
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
