Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why an error on Sumproduct?
Hi Team,
was wondering if you could shed some light on this for me. I have these 3 columns... A , E and P I use these countif statements to try and set up my Sumproduct.... =COUNTIF($E$2:$E$4000,"MST CSD") --results in a count of 405 =COUNTIF($A$2:$A$4000,V27) --results in a count of 33 Column P has an integer ranging from 1 - 40 I want to add colum P if E="MST CSD" and A = 18 (the value in V27) so I use =SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000)) But this results in an #N/A I know by using the Data filter that there are 8 occurances that meet the conditions and should be adding together to give a result of 141 any ideas of what I have done wrong? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why an error on Sumproduct?
=SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000))
Your formula should work but a "cleaner" way to write it would be: =SUMPRODUCT(--($A$2:$A$4000=V27),--($E$2:$E$4000="MST CSD"),$P$2:$P$4000) As far as the #N/A error, do you have any #N/A errors in any of the ranges? -- Biff Microsoft Excel MVP "Madduck" wrote in message ... Hi Team, was wondering if you could shed some light on this for me. I have these 3 columns... A , E and P I use these countif statements to try and set up my Sumproduct.... =COUNTIF($E$2:$E$4000,"MST CSD") --results in a count of 405 =COUNTIF($A$2:$A$4000,V27) --results in a count of 33 Column P has an integer ranging from 1 - 40 I want to add colum P if E="MST CSD" and A = 18 (the value in V27) so I use =SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000)) But this results in an #N/A I know by using the Data filter that there are 8 occurances that meet the conditions and should be adding together to give a result of 141 any ideas of what I have done wrong? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why an error on Sumproduct?
works okay for me except when there is #N/A error in the data somewhere.
"Madduck" wrote: Hi Team, was wondering if you could shed some light on this for me. I have these 3 columns... A , E and P I use these countif statements to try and set up my Sumproduct.... =COUNTIF($E$2:$E$4000,"MST CSD") --results in a count of 405 =COUNTIF($A$2:$A$4000,V27) --results in a count of 33 Column P has an integer ranging from 1 - 40 I want to add colum P if E="MST CSD" and A = 18 (the value in V27) so I use =SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000)) But this results in an #N/A I know by using the Data filter that there are 8 occurances that meet the conditions and should be adding together to give a result of 141 any ideas of what I have done wrong? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why an error on Sumproduct?
Thank you both... there was a sneaky little N/a I missed on checking .<
thanks again ...... "JMB" wrote: works okay for me except when there is #N/A error in the data somewhere. "Madduck" wrote: Hi Team, was wondering if you could shed some light on this for me. I have these 3 columns... A , E and P I use these countif statements to try and set up my Sumproduct.... =COUNTIF($E$2:$E$4000,"MST CSD") --results in a count of 405 =COUNTIF($A$2:$A$4000,V27) --results in a count of 33 Column P has an integer ranging from 1 - 40 I want to add colum P if E="MST CSD" and A = 18 (the value in V27) so I use =SUMPRODUCT(($E$2:$E$4000="MST CSD")*($A$2:$A$4000=V27),($P$2:$P$4000)) But this results in an #N/A I know by using the Data filter that there are 8 occurances that meet the conditions and should be adding together to give a result of 141 any ideas of what I have done wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT #N/A error | Excel Discussion (Misc queries) | |||
Sumproduct value error | Excel Worksheet Functions | |||
Sumproduct error | Excel Worksheet Functions | |||
SUMPRODUCT ERROR | Excel Discussion (Misc queries) | |||
Sumproduct #num error | Excel Worksheet Functions |