SUMPRODUCT with month number not working

sharky12345

Well-known Member
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))

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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))

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?

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.

ADVERTISEMENT
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.

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].

ADVERTISEMENT
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....

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!

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.

Thank you Mike!

Replies
7
Views
109
Replies
3
Views
249
Replies
15
Views
248
Replies
18
Views
193
Replies
7
Views
159

Forum statistics

Threads
1,196,459
Messages
6,015,372
Members
441,889
Latest member
balolaptopgiaolong

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back