How to ignore blank date cells in sumproduct
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? |
How to ignore blank date cells in sumproduct
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? |
How to ignore blank date cells in sumproduct
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? |
How to ignore blank date cells in sumproduct
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 |
How to ignore blank date cells in sumproduct
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? |
All times are GMT +1. The time now is 02:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com