| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to get rid of 0's and replace with Blanks
I have a formula that is an array I think which calculates how many products were sold in a month for a specific company. If no products are sold, it produces a $0.00. I would rather this left blank. Here is my array that I need to have produce a blank when it ends up as a 0. {=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shipping Log'!$A$2:$A$3255=C$1,'Shipping Log'!$M$2:$M$3255,0),0))} this formula is in box c13. If this will result in 0.00 how do I leave it blank instead? I know it has something to do with =if(???="","",sum.... but its not working for some reason. Can anyone help? -- Intuit ------------------------------------------------------------------------ Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901 View this thread: http://www.excelforum.com/showthread...hreadid=508271 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to get rid of 0's and replace with Blanks
Hi!
You don't need to use an array formula for that: =SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255) To suppress a zero return will make the formula twice as long: =IF(SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255)=0,"",SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255)) *OR* Use the first formula and format the cell to not display the zero: Custom format: 0;-0;;@ Note: the zero is still in the cell, it's just not being displayed. This might matter if you're doing other downstream calcs that use this cell. Biff "Intuit" wrote in message ... I have a formula that is an array I think which calculates how many products were sold in a month for a specific company. If no products are sold, it produces a $0.00. I would rather this left blank. Here is my array that I need to have produce a blank when it ends up as a 0. {=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shipping Log'!$A$2:$A$3255=C$1,'Shipping Log'!$M$2:$M$3255,0),0))} this formula is in box c13. If this will result in 0.00 how do I leave it blank instead? I know it has something to do with =if(???="","",sum.... but its not working for some reason. Can anyone help? -- Intuit ------------------------------------------------------------------------ Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901 View this thread: http://www.excelforum.com/showthread...hreadid=508271 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to get rid of 0's and replace with Blanks
Biff Wrote: Hi! You don't need to use an array formula for that: =SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255) To suppress a zero return will make the formula twice as long: =IF(SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255)=0,"",SUMPRODUCT(--('Shipping Log'!$D$2:$D$3255=$A3),--('Shipping Log'!$A$2:$A$3255=C$1),'Shipping Log'!$M$2:$M$3255)) *OR* Use the first formula and format the cell to not display the zero: Custom format: 0;-0;;@ Note: the zero is still in the cell, it's just not being displayed. This might matter if you're doing other downstream calcs that use this cell. Biff "Intuit" wrote in message ... I have a formula that is an array I think which calculates how many products were sold in a month for a specific company. If no products are sold, it produces a $0.00. I would rather this left blank. Here is my array that I need to have produce a blank when it ends up as a 0. {=SUM(IF('Shipping Log'!$D$2:$D$3255=$A3,IF('Shipping Log'!$A$2:$A$3255=C$1,'Shipping Log'!$M$2:$M$3255,0),0))} this formula is in box c13. If this will result in 0.00 how do I leave it blank instead? I know it has something to do with =if(???="","",sum.... but its not working for some reason. Can anyone help? -- Intuit ------------------------------------------------------------------------ Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901 View this thread: http://www.excelforum.com/showthread...hreadid=508271 Hey again Biff. Man you got this excel thing down pat! I obviously opted for the second option, but when I do the custom format, my $$$ go away. Anyway to still format these values (when there is a value) as currency? -- Intuit ------------------------------------------------------------------------ Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901 View this thread: http://www.excelforum.com/showthread...hreadid=508271 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Need to get rid of 0's and replace with Blanks
Intuit Wrote: Hey again Biff. Man you got this excel thing down pat! I obviously opted for the second option, but when I do the custom format, my $$$ go away. Anyway to still format these values (when there is a value) as currency? I figured it out. Thanks! -- Intuit ------------------------------------------------------------------------ Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901 View this thread: http://www.excelforum.com/showthread...hreadid=508271 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| replace "#DIV/0!" error with blanks | Excel Worksheet Functions | |||
| Search and replace | Excel Worksheet Functions | |||
| replace absolute references | Excel Worksheet Functions | |||
| Using Excel, how do I replace cells containing blanks with nulls? | Excel Discussion (Misc queries) | |||
| Replace Number with Text using Macro | Excel Discussion (Misc queries) | |||
Linear Mode
