| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct
Hi,
What do I need to add to the below formula, to get the return result as "zero", when one of the cell in column AO is not "Y". Prensently, the return result is #DIV/0!. =SUMPRODUCT(--('ES 0421'!$AC$2:$AC$1903=$B13),--('ES 0421'!$AO$2:$AO$1903="Y"),('ES 0421'!$AI$2:$AI$1903)/A2) Thanks, Priscilla |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct
You will only get #div/0! if A2 is 0.
"Priscilla" wrote: Hi, What do I need to add to the below formula, to get the return result as "zero", when one of the cell in column AO is not "Y". Prensently, the return result is #DIV/0!. =SUMPRODUCT(--('ES 0421'!$AC$2:$AC$1903=$B13),--('ES 0421'!$AO$2:$AO$1903="Y"),('ES 0421'!$AI$2:$AI$1903)/A2) Thanks, Priscilla |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct
Yes cell A2 is "zero", is there anyway I can get around that.
"Toppers" wrote: You will only get #div/0! if A2 is 0. "Priscilla" wrote: Hi, What do I need to add to the below formula, to get the return result as "zero", when one of the cell in column AO is not "Y". Prensently, the return result is #DIV/0!. =SUMPRODUCT(--('ES 0421'!$AC$2:$AC$1903=$B13),--('ES 0421'!$AO$2:$AO$1903="Y"),('ES 0421'!$AI$2:$AI$1903)/A2) Thanks, Priscilla |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sumproduct
=IF (A2<0,SUMPRODUCT(--('ES 0421'!$AC$2:$AC$1903=$B13),--('ES 0421'!$AO$2:$AO$1903="Y"),('ES 0421'!$AI$2:$AI$1903)/A2),0) this will set cell to 0 if A2 is 0 HTH "Priscilla" wrote: Yes cell A2 is "zero", is there anyway I can get around that. "Toppers" wrote: You will only get #div/0! if A2 is 0. "Priscilla" wrote: Hi, What do I need to add to the below formula, to get the return result as "zero", when one of the cell in column AO is not "Y". Prensently, the return result is #DIV/0!. =SUMPRODUCT(--('ES 0421'!$AC$2:$AC$1903=$B13),--('ES 0421'!$AO$2:$AO$1903="Y"),('ES 0421'!$AI$2:$AI$1903)/A2) Thanks, Priscilla |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
| sumproduct causing memory errors? | Excel Worksheet Functions | |||
| Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
