Leaving a Cell Blank and Sumproduct Function

[jaclrsen]

Hi, I have a worksheet called 'Overall' which adds totals for years relating to dates in other worksheets ('Sell and Purchases') using a SUMPRODUCT Function.

This works fine but I'm looking for cells to be blank rather than 0 if all of the referencing cells for that particular year are blank in that worksheet. So I need an IF Function but I'm not sure how to do this.

Take the year 2012 for example in the Overall Sheet:

Total - 2012 (Cell D7 - Overall Worksheet)

* If 'Sell' and 'Negative' for 2012 are blank, Overall Total equals blank.
* If 'Sell' or Negative' equals 0 or more for 2012, sum takes places with additions.

Sell - 2012 (Cell B7 - Overall Worksheet)

* If 2012 has no date entries in 'Sell' Worksheet, this will be blank.
* If entries in 'Sell' Worksheet equal 0 or more, sum takes place with additions for this date.

Purchases - 2012 (Cell C7 - Overall Worksheet)

* If 2012 has no date entries in 'Purchases' Worksheet, this will be blank.
* If entries in 'Purchases' Worksheet equal 0 or more, sum takes place with additions for this date.

Please find attached sample workbook.

Any help with this would be great. Thanks.

[Ace_XL]

In B7 use

Please Login or Register  to view this content.

In C7 use

Please Login or Register  to view this content.

In D7 use the existing

Please Login or Register  to view this content.

Note: This way you wont have any blanks. In case of no matching year the formula simply returns zero.

[jaclrsen]

Hi Ace_XL, Thank you for your excellent response.

I know I'm odd but is it possible to return blsnk cells instead of the zeroes if there are no entries?

For example, if there are no entries for a year like 2012 across both 'Purchases' and 'Sell' worksheets, is it possible to return blank cells? Thus meaning the cells for Sell, Negative and Total are blank by default if there are no entries for that year. Thanks for your help.

[Ace_XL]

Alright then..use these

In B7

Please Login or Register  to view this content.

In C7

Please Login or Register  to view this content.

In D7

Please Login or Register  to view this content.

In D7

[jaclrsen]

Hi Ace_XL, Thank you so much! Your solutions are exactly what I was looking for. I was trying formulas which were like paragraphs but your excellent formulas are so much better and they work. I'm so grateful for your help with my post. Thank you!