I have a worksheet in which I am tring to sum the data based upon a code
range, a date range and cost range. However when I enter this formula it sums
the data from blank cells into the cell i wish to sum all the costs for
January.

=SUMPRODUCT(--($B$2:$B$268=$U277),--(MONTH($D$2:$D$268)=8),($K$2:$K$268))

Is there any way around this?

Hi,

I can't make that formula misbehave, it looks fine to me can you give the
precise circumstances under which you get an error.

Mike

"Phil_S" wrote:

I have a worksheet in which I am tring to sum the data based upon a code
range, a date range and cost range. However when I enter this formula it sums
the data from blank cells into the cell i wish to sum all the costs for
January.

=SUMPRODUCT(--($B$2:$B$268=$U277),--(MONTH($D$2:$D$268)=8),($K$2:$K$268))

Is there any way around this?

Thanks for the reponse Mike.

I have...

CODE ORDER DATE COST
CS01 7-Jan-08 £200.00
MT03 14-Jan-08 £4000.00
MT03 (blank) £500.00

This is a shortened example

The formula is fine but my problem is when a cell in the date column is
blank it assumes that it is the 00-Jan-00 and totals up the data in my
January totals Table.

I would like it to ignore the blank cells. Is this Possible?

"Mike H" wrote:

Hi,

I can't make that formula misbehave, it looks fine to me can you give the
precise circumstances under which you get an error.

Mike

"Phil_S" wrote:

I have a worksheet in which I am tring to sum the data based upon a code
range, a date range and cost range. However when I enter this formula it sums
the data from blank cells into the cell i wish to sum all the costs for
January.

=SUMPRODUCT(--($B$2:$B$268=$U277),--(MONTH($D$2:$D$268)=8),($K$2:$K$268))

Is there any way around this?

Check to see if there's something numeric in that D range:

=SUMPRODUCT(--($B$2:$B$268=$U277),
--(isnumber($d$2:$d$268),
--(MONTH($D$2:$D$268)=1),
($K$2:$K$268))

Phil_S wrote:

I have a worksheet in which I am tring to sum the data based upon a code
range, a date range and cost range. However when I enter this formula it sums
the data from blank cells into the cell i wish to sum all the costs for
January.

=SUMPRODUCT(--($B$2:$B$268=$U277),--(MONTH($D$2:$D$268)=8),($K$2:$K$268))

Is there any way around this?

--

Dave Peterson

Try this array formula

=SUM(IF((B2:B4=A1)*(LEN(TRIM(C2:C4))0)*(ISNUMBER( C2:C4)*(MONTH(C2:C4)=1)),D2:D4))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Phil_S" wrote in message
...
Thanks for the reponse Mike.

I have...

CODE ORDER DATE COST
CS01 7-Jan-08 £200.00
MT03 14-Jan-08 £4000.00
MT03 (blank) £500.00

This is a shortened example

The formula is fine but my problem is when a cell in the date column is
blank it assumes that it is the 00-Jan-00 and totals up the data in my
January totals Table.

I would like it to ignore the blank cells. Is this Possible?

"Mike H" wrote:

Hi,

I can't make that formula misbehave, it looks fine to me can you give the
precise circumstances under which you get an error.

Mike

"Phil_S" wrote:

I have a worksheet in which I am tring to sum the data based upon a
code
range, a date range and cost range. However when I enter this formula
it sums
the data from blank cells into the cell i wish to sum all the costs for
January.

=SUMPRODUCT(--($B$2:$B$268=$U277),--(MONTH($D$2:$D$268)=8),($K$2:$K$268))

Is there any way around this?

You could just add one more part tp your formula:
=SUMPRODUCT(--($B$2:$B$268=$U277),--(MONTH($D$2:$D$268)=8),--($D$2:$d$268<""),($K$2:$K$268))

--
John C


"Phil_S" wrote:

Thanks for the reponse Mike.

I have...

CODE ORDER DATE COST
CS01 7-Jan-08 £200.00
MT03 14-Jan-08 £4000.00
MT03 (blank) £500.00

This is a shortened example

The formula is fine but my problem is when a cell in the date column is
blank it assumes that it is the 00-Jan-00 and totals up the data in my
January totals Table.

I would like it to ignore the blank cells. Is this Possible?

"Mike H" wrote:

Hi,

I can't make that formula misbehave, it looks fine to me can you give the
precise circumstances under which you get an error.

Mike

"Phil_S" wrote:

I have a worksheet in which I am tring to sum the data based upon a code
range, a date range and cost range. However when I enter this formula it sums
the data from blank cells into the cell i wish to sum all the costs for
January.

=SUMPRODUCT(--($B$2:$B$268=$U277),--(MONTH($D$2:$D$268)=8),($K$2:$K$268))

Is there any way around this?