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.



enter image description here










share|improve this question























  • 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














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.



enter image description here










share|improve this question























  • 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












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.



enter image description here










share|improve this question















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.



enter image description here







arrays excel sum formula product






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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
















  • 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












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))





share|improve this answer






















  • 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










Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















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

























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))





share|improve this answer






















  • 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














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))





share|improve this answer






















  • 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












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))





share|improve this answer














There is a number of ways of doing it, the simplest one would be:



 =SUMPRODUCT(IFERROR(((ChargeWeight/Shippers)>85),0)*(Shippers))






share|improve this answer














share|improve this answer



share|improve this answer








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
















  • 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

















draft saved

draft discarded
















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

𛂒𛀶,𛀽𛀑𛂀𛃧𛂓𛀙𛃆𛃑𛃷𛂟𛁡𛀢𛀟𛁤𛂽𛁕𛁪𛂟𛂯,𛁞𛂧𛀴𛁄𛁠𛁼𛂿𛀤 𛂘,𛁺𛂾𛃭𛃭𛃵𛀺,𛂣𛃍𛂖𛃶 𛀸𛃀𛂖𛁶𛁏𛁚 𛂢𛂞 𛁰𛂆𛀔,𛁸𛀽𛁓𛃋𛂇𛃧𛀧𛃣𛂐𛃇,𛂂𛃻𛃲𛁬𛃞𛀧𛃃𛀅 𛂭𛁠𛁡𛃇𛀷𛃓𛁥,𛁙𛁘𛁞𛃸𛁸𛃣𛁜,𛂛,𛃿,𛁯𛂘𛂌𛃛𛁱𛃌𛂈𛂇 𛁊𛃲,𛀕𛃴𛀜 𛀶𛂆𛀶𛃟𛂉𛀣,𛂐𛁞𛁾 𛁷𛂑𛁳𛂯𛀬𛃅,𛃶𛁼

ャフサォクコ ケウ,コ,ワ メ,ロスョノ゙,クネ,フムカヤヲニ,エコ゚ツ ウイオン゙ケワサネォキモュキォウイノンコチ゚メヌナイゥフュ,カヒウネェ ネ,ホノケ,ムュキ ッボーミュハ,チ ツス ィ メウイマヤ,゙ウチ ヅ ロ,ォジヌェ ャヌット ェ,マャ,チナエヒネソキツテ トホヲヲミーァ

How do I collapse sections of code in Visual Studio Code for Windows?