I am trying to calculate a quarterly profit margin percentage using the formula =(SUMPRODUCT(('PL Updated'!$AZ$8:$CI$38*('PL Updated'!$AZ$7:$CI$7=$AR$1))*('PL Updated'!$C$8:$C$38=$B6))-(SUMPRODUCT(('PL Updated'!$AZ$43:$CI$65*('PL Updated'!$AZ$7:$CI$7=$AR$1))*('PL Updated'!$C$43:$C$65=$B6))))/SUMPRODUCT(('PL Updated'!$AZ$8:$CI$38*('PL Updated'!$AZ$7:$CI$7=$AR$1))*('PL Updated'!$C$8:$C$38=$B6)) but it is giving me a DIV error. The formula was working for a monthly basis with =(SUMPRODUCT(('PL Updated'!$AZ$8:$CI$38*('PL Updated'!$AZ$7:$CI$7=$C$1))*('PL Updated'!$C$8:$C$38=$B6))-(SUMPRODUCT(('PL Updated'!$AZ$43:$CI$65*('PL Updated'!$AZ$7:$CI$7=$AK$1))*('PL Updated'!$C$43:$C$65=$B6))))/SUMPRODUCT(('PL Updated'!$AZ$8:$CI$38*('PL Updated'!$AZ$7:$CI$7=$C$1))*('PL Updated'!$C$8:$C$38=$B6)). The month in this formula is C1 and AK1 (which is the same as C1). The quarter which is suppose to reference three columns on the different sheet is AR1. B6 is the brand which should stay the same. Am I using SUMPRODUCT wrong in the quarter instance? Thanks in advance!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Trying to calculate quarterly margin using SUMPRODUCT but getting DIV error
- Thread starter jlawrence
- Start date
6StringJazzer
Well-known Member
- Joined
- Jan 27, 2010
- Messages
- 3,286
- Office Version
- 365
- Platform
- Windows
- Mobile
It is not possible to diagnose this without knowing what data is where and values you are dealing with.
Upvote
0
Similar threads
- Question
