[SOLVED] Sum Product Function- Blank Cells

[joshmd9909]

I am working with the sum production function & I can't figure out how to get
the function to return a blank cell with no data is in the specified cell.

Here is the function I'm using.

SUMPRODUCT((A2:A450=A7)*(B1:BG1=B6)*(B2:BG450))

Column A2- includes ID #'s, while row 1 includes dates. The data set
(B2-B45) has some blank cells, & I want it to return a blank, & not a 0,
however some cells have a 0, and I want the 0 to be returned.

I feel I'm missing something really obvious. Any help would be greatly
appreciated!

Thanks,
Josh

[JR]

If you want the formula to return a blank where it is returning a zero, try
this...

IF(SUMPRODUCT((A2:A450=A7)*(B1:BG1=B6)*(B2:BG450))=0,"
",SUMPRODUCT((A2:A450=A7)*(B1:BG1=B6)*(B2:BG450)))

Basically, this says... if formula equal zero, then blank, otherwise formula.

"joshmd9909" wrote:

> I am working with the sum production function & I can't figure out how to get
> the function to return a blank cell with no data is in the specified cell.
>
> Here is the function I'm using.
>
> SUMPRODUCT((A2:A450=A7)*(B1:BG1=B6)*(B2:BG450))
>
> Column A2- includes ID #'s, while row 1 includes dates. The data set
> (B2-B45) has some blank cells, & I want it to return a blank, & not a 0,
> however some cells have a 0, and I want the 0 to be returned.
>
> I feel I'm missing something really obvious. Any help would be greatly
> appreciated!
>
> Thanks,
> Josh

[joshmd9909]

JR,

This will not return a value for cells that are blank which is great!!! But
some cells have a value of 0 & I want the 0 returned. There are also some
negative numbers. How could I got about doing this?

Thanks very much for your help!!!

Josh

"JR" wrote:

> If you want the formula to return a blank where it is returning a zero, try
> this...
>
> IF(SUMPRODUCT((A2:A450=A7)*(B1:BG1=B6)*(B2:BG450))=0,"
> ",SUMPRODUCT((A2:A450=A7)*(B1:BG1=B6)*(B2:BG450)))
>
> Basically, this says... if formula equal zero, then blank, otherwise formula.
>
> "joshmd9909" wrote:
>
> > I am working with the sum production function & I can't figure out how to get
> > the function to return a blank cell with no data is in the specified cell.
> >
> > Here is the function I'm using.
> >
> > SUMPRODUCT((A2:A450=A7)*(B1:BG1=B6)*(B2:BG450))
> >
> > Column A2- includes ID #'s, while row 1 includes dates. The data set
> > (B2-B45) has some blank cells, & I want it to return a blank, & not a 0,
> > however some cells have a 0, and I want the 0 to be returned.
> >
> > I feel I'm missing something really obvious. Any help would be greatly
> > appreciated!
> >
> > Thanks,
> > Josh