I have a workbook that gives the %age profit of products sold on a month basis. Each month has a separate worksheet to allow for identifcation of variances from expected on a monthly basis.

I want to summarise the monthly data by supplier and have created a worksheet using the SUMIF function to pull the information through from the individual monthly sheets. =SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])

Unfortunately I've hit a bit of a brick wall, we don't sell all the lines every month and I am getting a DIV/0 error.

Is there any way to return these cells as a blank?

Thanks for any help you can give


Submitted via EggHeadCafe - Software Developer Portal of Choice
COM+ Services Overhead in .NET
http://www.eggheadcafe.com/tutorials...erhead-in.aspx

=IFERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales]),"")



---
HTH

Bob Phillips

<Helena Pereira wrote in message
...
I have a workbook that gives the %age profit of products sold on a month
basis. Each month has a separate worksheet to allow for identifcation of
variances from expected on a monthly basis.

I want to summarise the monthly data by supplier and have created a
worksheet using the SUMIF function to pull the information through from
the individual monthly sheets.
=SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])

Unfortunately I've hit a bit of a brick wall, we don't sell all the lines
every month and I am getting a DIV/0 error.

Is there any way to return these cells as a blank?

Thanks for any help you can give


Submitted via EggHeadCafe - Software Developer Portal of Choice
COM+ Services Overhead in .NET
http://www.eggheadcafe.com/tutorials...erhead-in.aspx

Try this

=IF(ISERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])),"",(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])))



"Helena Pereira" wrote:

I have a workbook that gives the %age profit of products sold on a month basis. Each month has a separate worksheet to allow for identifcation of variances from expected on a monthly basis.

I want to summarise the monthly data by supplier and have created a worksheet using the SUMIF function to pull the information through from the individual monthly sheets. =SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])

Unfortunately I've hit a bit of a brick wall, we don't sell all the lines every month and I am getting a DIV/0 error.

Is there any way to return these cells as a blank?

Thanks for any help you can give


Submitted via EggHeadCafe - Software Developer Portal of Choice
COM+ Services Overhead in .NET
http://www.eggheadcafe.com/tutorials...erhead-in.aspx
.

I never use that structured syntax...

Wouldn't the result of that formula always be 1 (provided it doesn't
generate an error)?

--
Biff
Microsoft Excel MVP


"Bob Phillips" wrote in message
...

=IFERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales]),"")



---
HTH

Bob Phillips

<Helena Pereira wrote in message
...
I have a workbook that gives the %age profit of products sold on a month
basis. Each month has a separate worksheet to allow for identifcation of
variances from expected on a monthly basis.

I want to summarise the monthly data by supplier and have created a
worksheet using the SUMIF function to pull the information through from
the individual monthly sheets.
=SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])

Unfortunately I've hit a bit of a brick wall, we don't sell all the lines
every month and I am getting a DIV/0 error.

Is there any way to return these cells as a blank?

Thanks for any help you can give


Submitted via EggHeadCafe - Software Developer Portal of Choice
COM+ Services Overhead in .NET
http://www.eggheadcafe.com/tutorials...erhead-in.aspx

The formula is working.
thansk for the advise



Bob Phillips wrote:

=IFERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sa
08-Jan-10

=IFERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales]),"")



---
HTH

Bob Phillips

<Helena Pereira wrote in message

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Some Useful Javascript Debugging Functions
http://www.eggheadcafe.com/tutorials...script-de.aspx

This formula works too, thanks for your help.



walrus wrote:

Try
08-Jan-10

Try this

=IF(ISERROR(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])),"",(SUMIF([Profit],[SupplerName],[Sales])/SUMIF([Profit],[SupplerName],[Sales])))



"Helena Pereira" wrote:

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
SQL Server Query Analyzer Runs Fast - Stored Procedure Runs Slow
http://www.eggheadcafe.com/tutorials...-analyzer.aspx