SUMPRODUCT with month number not working

[sharky12345]

Does anyone know why this doesn't work in Excel 2003?

It's giving me a '#VALUE!'

=SUMPRODUCT((MONTH(Data!$A$2:$A$500)=1)*(Data!$C$2:$C$500))

 

[Mike LH]

Hi,

The formula is nearly fine but it will return a value error if there's text in either column A or Column C or if there are any #VALUE! errors in the range.

You should also test for empty cells in Col A because an empty cell will evaluate as month 1


=SUMPRODUCT((MONTH(Data!$A$2:$A$500)=1)*(A$2:$A$500<>"")*(Data!$C$2:$C$500))

 

[Aladin Akyurek]

Does anyone know why this doesn't work in Excel 2003?

It's giving me a '#VALUE!'

Does this re-write...

=SUMPRODUCT(Data!$C$2:$C$500,--(MONTH(Data!$A$2:$A$500)=1),--ISNUMBER(Data!$A$2:$A$500))

help?

 

[sharky12345]

Gents, thanks to both - I'm still getting the error, perhaps I should say what's in each range so you can see if it will make a difference?

Column A is a date, formatted as such 'dd/mm/yyyy/ Column C is time formatted as '[h:mm]'

Additionally, there may be blank cells in A.

 

[Mike LH]

Hi,

You need to check very carefully all the data in Columns A & C. It all might look like dates and times but almost certainly some of them are text values. Try this in an empty cell

=ISNUMBER(A2)
and
=ISNUMBER(D2)

Drag both down and they should all return TRUE. If any return FALSE then it's a text value. In addition you need to format the formula cell as


[HH]:mm

The square brackets will stop the time rolling over to zero if it exceeds 24 hours.

 

[Aladin Akyurek]

Gents, thanks to both - I'm still getting the error, perhaps I should say what's in each range so you can see if it will make a difference?

Column A is a date, formatted as such 'dd/mm/yyyy/ Column C is time formatted as '[h:mm]'

Additionally, there may be blank cells in A.

In that case, switch to an array-processing formula with Sum and If...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(Data!$A$2:$A$500),IF(MONTH(Data!$A$2:$A$500)=1,Data!$C$2:$C$500)))

The formula cell must be formatted as [h:mm].

 

[sharky12345]

Aladin,

Your suggestion gives me '0:00' when it should show '20:00'.

Mike - I have checked as you suggested and I do get some 'FALSE' within the range so guess that's what is causing the issue. What's the easiest way around it? The values are input from a userform so if I have to format them or the cell as part of the enter process then that's what I'll do, if it's the easiest solution of course....

 

[sharky12345]

Gents, ignore that last post - I realised that I hadn't entered it as an Array Formula and now it seems to work so thank you!

 

[Mike LH]

Aladin,

Your suggestion gives me '0:00' when it should show '20:00'.

Mike - I have checked as you suggested and I do get some 'FALSE' within the range so guess that's what is causing the issue. What's the easiest way around it? The values are input from a userform so if I have to format them or the cell as part of the enter process then that's what I'll do, if it's the easiest solution of course....

You need to correct the errors and convert the text values to numbers, or you may return an incorrect answer for your formula. Put a 1 in an empty cell, select that cell and right click and copy. Select the dates and times columns and right click, paste special, select MULTIPLY and click OK and they should convert to numbers. After you've done that you may need to reformat the 2 columns as dates and time.

 

[sharky12345]

Thank you Mike!