Sum Product Errors in Excel Errors
up vote
0
down vote
favorite
I have this formula and it is giving me grief, I keep getting errors (#DIV/0!). I have checked online and apparently sum product does not like blanks. I was wondering if someone could help me out on how I can get sumproduct to skip the blank cells.
=SUMPRODUCT((N2:N8/M2:M8>85)*(M2:M8))
Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT "divided by" ORDERS>85 then sum. Hopefully this makes sense.
Also Note, this is just a sample data, the actual data can be thousands and thousands of rows.

arrays excel sum formula product
add a comment |
up vote
0
down vote
favorite
I have this formula and it is giving me grief, I keep getting errors (#DIV/0!). I have checked online and apparently sum product does not like blanks. I was wondering if someone could help me out on how I can get sumproduct to skip the blank cells.
=SUMPRODUCT((N2:N8/M2:M8>85)*(M2:M8))
Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT "divided by" ORDERS>85 then sum. Hopefully this makes sense.
Also Note, this is just a sample data, the actual data can be thousands and thousands of rows.

arrays excel sum formula product
Possible duplicate of Excel formula -> how to change SUMPRODUCT formula to skip null cells. Also did you search for anything first?
– BruceWayne
Nov 9 at 4:54
Thanks, I have tried it before posting and it's not providing me with the answer I needed. I responded to another person with more information RE my question.
– Fred
Nov 9 at 5:48
To start, "I keep getting errors": The reason you get these errors are not to annoy you - they are there to provide you useful information. And because this information is useful, it would also help us with your question. So please edit your question and tell us exactly what errors you are seeing.
– K.Dᴀᴠɪs
Nov 9 at 6:20
Thanks for your help, I have updated my question. Please let me know if you need anymore information.
– Fred
Nov 9 at 6:34
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have this formula and it is giving me grief, I keep getting errors (#DIV/0!). I have checked online and apparently sum product does not like blanks. I was wondering if someone could help me out on how I can get sumproduct to skip the blank cells.
=SUMPRODUCT((N2:N8/M2:M8>85)*(M2:M8))
Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT "divided by" ORDERS>85 then sum. Hopefully this makes sense.
Also Note, this is just a sample data, the actual data can be thousands and thousands of rows.

arrays excel sum formula product
I have this formula and it is giving me grief, I keep getting errors (#DIV/0!). I have checked online and apparently sum product does not like blanks. I was wondering if someone could help me out on how I can get sumproduct to skip the blank cells.
=SUMPRODUCT((N2:N8/M2:M8>85)*(M2:M8))
Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT "divided by" ORDERS>85 then sum. Hopefully this makes sense.
Also Note, this is just a sample data, the actual data can be thousands and thousands of rows.

arrays excel sum formula product
arrays excel sum formula product
edited Nov 9 at 6:53
asked Nov 9 at 4:47
Fred
1617
1617
Possible duplicate of Excel formula -> how to change SUMPRODUCT formula to skip null cells. Also did you search for anything first?
– BruceWayne
Nov 9 at 4:54
Thanks, I have tried it before posting and it's not providing me with the answer I needed. I responded to another person with more information RE my question.
– Fred
Nov 9 at 5:48
To start, "I keep getting errors": The reason you get these errors are not to annoy you - they are there to provide you useful information. And because this information is useful, it would also help us with your question. So please edit your question and tell us exactly what errors you are seeing.
– K.Dᴀᴠɪs
Nov 9 at 6:20
Thanks for your help, I have updated my question. Please let me know if you need anymore information.
– Fred
Nov 9 at 6:34
add a comment |
Possible duplicate of Excel formula -> how to change SUMPRODUCT formula to skip null cells. Also did you search for anything first?
– BruceWayne
Nov 9 at 4:54
Thanks, I have tried it before posting and it's not providing me with the answer I needed. I responded to another person with more information RE my question.
– Fred
Nov 9 at 5:48
To start, "I keep getting errors": The reason you get these errors are not to annoy you - they are there to provide you useful information. And because this information is useful, it would also help us with your question. So please edit your question and tell us exactly what errors you are seeing.
– K.Dᴀᴠɪs
Nov 9 at 6:20
Thanks for your help, I have updated my question. Please let me know if you need anymore information.
– Fred
Nov 9 at 6:34
Possible duplicate of Excel formula -> how to change SUMPRODUCT formula to skip null cells. Also did you search for anything first?
– BruceWayne
Nov 9 at 4:54
Possible duplicate of Excel formula -> how to change SUMPRODUCT formula to skip null cells. Also did you search for anything first?
– BruceWayne
Nov 9 at 4:54
Thanks, I have tried it before posting and it's not providing me with the answer I needed. I responded to another person with more information RE my question.
– Fred
Nov 9 at 5:48
Thanks, I have tried it before posting and it's not providing me with the answer I needed. I responded to another person with more information RE my question.
– Fred
Nov 9 at 5:48
To start, "I keep getting errors": The reason you get these errors are not to annoy you - they are there to provide you useful information. And because this information is useful, it would also help us with your question. So please edit your question and tell us exactly what errors you are seeing.
– K.Dᴀᴠɪs
Nov 9 at 6:20
To start, "I keep getting errors": The reason you get these errors are not to annoy you - they are there to provide you useful information. And because this information is useful, it would also help us with your question. So please edit your question and tell us exactly what errors you are seeing.
– K.Dᴀᴠɪs
Nov 9 at 6:20
Thanks for your help, I have updated my question. Please let me know if you need anymore information.
– Fred
Nov 9 at 6:34
Thanks for your help, I have updated my question. Please let me know if you need anymore information.
– Fred
Nov 9 at 6:34
add a comment |
1 Answer
1
active
oldest
votes
up vote
2
down vote
accepted
There is a number of ways of doing it, the simplest one would be:
=SUMPRODUCT(IFERROR(((ChargeWeight/Shippers)>85),0)*(Shippers))
Thanks for your response, but its not providing me with the result that I would like. apologies, I didn't provide you with all the information. Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT/ORDERS>85 then sum. Hopefully this makes sense.
– Fred
Nov 9 at 5:46
Corrected, please test. It's an array formula, so enter with Ctrl-Shift
– Michal Rosa
Nov 9 at 7:33
Awesome, worked! Thanks!
– Fred
Nov 9 at 7:40
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
accepted
There is a number of ways of doing it, the simplest one would be:
=SUMPRODUCT(IFERROR(((ChargeWeight/Shippers)>85),0)*(Shippers))
Thanks for your response, but its not providing me with the result that I would like. apologies, I didn't provide you with all the information. Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT/ORDERS>85 then sum. Hopefully this makes sense.
– Fred
Nov 9 at 5:46
Corrected, please test. It's an array formula, so enter with Ctrl-Shift
– Michal Rosa
Nov 9 at 7:33
Awesome, worked! Thanks!
– Fred
Nov 9 at 7:40
add a comment |
up vote
2
down vote
accepted
There is a number of ways of doing it, the simplest one would be:
=SUMPRODUCT(IFERROR(((ChargeWeight/Shippers)>85),0)*(Shippers))
Thanks for your response, but its not providing me with the result that I would like. apologies, I didn't provide you with all the information. Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT/ORDERS>85 then sum. Hopefully this makes sense.
– Fred
Nov 9 at 5:46
Corrected, please test. It's an array formula, so enter with Ctrl-Shift
– Michal Rosa
Nov 9 at 7:33
Awesome, worked! Thanks!
– Fred
Nov 9 at 7:40
add a comment |
up vote
2
down vote
accepted
up vote
2
down vote
accepted
There is a number of ways of doing it, the simplest one would be:
=SUMPRODUCT(IFERROR(((ChargeWeight/Shippers)>85),0)*(Shippers))
There is a number of ways of doing it, the simplest one would be:
=SUMPRODUCT(IFERROR(((ChargeWeight/Shippers)>85),0)*(Shippers))
edited Nov 9 at 7:32
answered Nov 9 at 5:19
Michal Rosa
1,128614
1,128614
Thanks for your response, but its not providing me with the result that I would like. apologies, I didn't provide you with all the information. Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT/ORDERS>85 then sum. Hopefully this makes sense.
– Fred
Nov 9 at 5:46
Corrected, please test. It's an array formula, so enter with Ctrl-Shift
– Michal Rosa
Nov 9 at 7:33
Awesome, worked! Thanks!
– Fred
Nov 9 at 7:40
add a comment |
Thanks for your response, but its not providing me with the result that I would like. apologies, I didn't provide you with all the information. Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT/ORDERS>85 then sum. Hopefully this makes sense.
– Fred
Nov 9 at 5:46
Corrected, please test. It's an array formula, so enter with Ctrl-Shift
– Michal Rosa
Nov 9 at 7:33
Awesome, worked! Thanks!
– Fred
Nov 9 at 7:40
Thanks for your response, but its not providing me with the result that I would like. apologies, I didn't provide you with all the information. Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT/ORDERS>85 then sum. Hopefully this makes sense.
– Fred
Nov 9 at 5:46
Thanks for your response, but its not providing me with the result that I would like. apologies, I didn't provide you with all the information. Basically column M is a list of orders while N is a list of the total weight of those orders. I need to sum the orders column if (Charge Weight/Orders) is greater than 85. so basically IF CHARGE WEIGHT/ORDERS>85 then sum. Hopefully this makes sense.
– Fred
Nov 9 at 5:46
Corrected, please test. It's an array formula, so enter with Ctrl-Shift
– Michal Rosa
Nov 9 at 7:33
Corrected, please test. It's an array formula, so enter with Ctrl-Shift
– Michal Rosa
Nov 9 at 7:33
Awesome, worked! Thanks!
– Fred
Nov 9 at 7:40
Awesome, worked! Thanks!
– Fred
Nov 9 at 7:40
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53220015%2fsum-product-errors-in-excel-errors%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Possible duplicate of Excel formula -> how to change SUMPRODUCT formula to skip null cells. Also did you search for anything first?
– BruceWayne
Nov 9 at 4:54
Thanks, I have tried it before posting and it's not providing me with the answer I needed. I responded to another person with more information RE my question.
– Fred
Nov 9 at 5:48
To start, "I keep getting errors": The reason you get these errors are not to annoy you - they are there to provide you useful information. And because this information is useful, it would also help us with your question. So please edit your question and tell us exactly what errors you are seeing.
– K.Dᴀᴠɪs
Nov 9 at 6:20
Thanks for your help, I have updated my question. Please let me know if you need anymore information.
– Fred
Nov 9 at 6:34