Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with SUMPRODUCT
I have used the SUMPRODUCT to add up how many times each month an project is
updated. The formula works from months Feb through Dec, although for Jan, it gives me a number which I cannot understand. The formula for Feb is =SUMPRODUCT(--(MONTH(B2:B49)=2)). I typed the same formula in Jan and just changed the number 2 for Feb to a number 1 for Jan. Why does it give me 44 and how could I fix it? SPL # Date Time 7006 28-Feb 17:30 Jan 44 7007 20-Mar 19:30 Feb 1 7008 12-May 10:30 Mar 1 7009 6-Jun 22:00 Apr 0 May 1 Jun 1 Jul 0 Aug 0 Sep 0 Oct 0 Nov 0 Dec 0 Thank you in advance! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with SUMPRODUCT
Try
=SUMPRODUCT(--(MONTH(B2:B49)=1),--(B2:B49<"")) excel sees blanks as zero and excel dates start with the fictive date Jan 0 1900 and since blanks are seen as zeros in array formulas you have to either use ISNUMBER, <"" or add the YEAR as well -- Regards, Peo Sjoblom "stevesoul" wrote in message ... I have used the SUMPRODUCT to add up how many times each month an project is updated. The formula works from months Feb through Dec, although for Jan, it gives me a number which I cannot understand. The formula for Feb is =SUMPRODUCT(--(MONTH(B2:B49)=2)). I typed the same formula in Jan and just changed the number 2 for Feb to a number 1 for Jan. Why does it give me 44 and how could I fix it? SPL # Date Time 7006 28-Feb 17:30 Jan 44 7007 20-Mar 19:30 Feb 1 7008 12-May 10:30 Mar 1 7009 6-Jun 22:00 Apr 0 May 1 Jun 1 Jul 0 Aug 0 Sep 0 Oct 0 Nov 0 Dec 0 Thank you in advance! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with SUMPRODUCT
If the cell (say A1) is empty, then excel will return a 1 for =month(a1).
You can avoid counting those empty cells as January: =SUMPRODUCT(--(isnumber(b2:b49),--(MONTH(B2:B49)=1)) stevesoul wrote: I have used the SUMPRODUCT to add up how many times each month an project is updated. The formula works from months Feb through Dec, although for Jan, it gives me a number which I cannot understand. The formula for Feb is =SUMPRODUCT(--(MONTH(B2:B49)=2)). I typed the same formula in Jan and just changed the number 2 for Feb to a number 1 for Jan. Why does it give me 44 and how could I fix it? SPL # Date Time 7006 28-Feb 17:30 Jan 44 7007 20-Mar 19:30 Feb 1 7008 12-May 10:30 Mar 1 7009 6-Jun 22:00 Apr 0 May 1 Jun 1 Jul 0 Aug 0 Sep 0 Oct 0 Nov 0 Dec 0 Thank you in advance! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct problem | New Users to Excel | |||
Sumproduct problem... | Excel Worksheet Functions | |||
Problem with SumProduct | Excel Discussion (Misc queries) | |||
Sumproduct Problem | Excel Worksheet Functions | |||
SUMPRODUCT problem | Excel Worksheet Functions |