If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|