Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT #VALUE! error
Hello. this formula
=SUMPRODUCT((MONTH('G Codes'!D6:D300)=MONTH(E5))*('G Codes'! C6:C300=C103)*('G Codes'!J6:J300)) sums the amounts in column J of 'G Codes' if the month on the 'G Codes' worksheet matches the on in E5 and the text in column C matches the text in C103 Now the strange thing is that for another worksheet the exact same formula works out fine. For the 'G Codes' worksheet it produces the #VALUE! error. The formulae are looking in the right place- there are no extra columns in this worksheet. I've checked the date and number columns for 'spaces' (ie text entries), and checked that the date formats are correct. I believe that there's a different way of writing the formula if you're exclusively using numbers but I need my formula to compare text. Mainly I don't understand why this works fine for one worksheet and not the other, and also that all the cells referring to the G Codes in this worksheet show #VALUE! (although in another worksheet they are referred to without any problems)... Can anyone help? Many thanks Chris |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT #VALUE! error
I would suggest that a piece of data in G Codes!D6:D300 cannot be converted
to a "Month" value You say that you have checked Date FORMATS - have you checked to see whwther you have "real" dates or textual representations of dates ? -- Rgds, Geoff "A crash reduces Your expensive computer To a simple stone" "Chris Slowe" wrote: Hello. this formula =SUMPRODUCT((MONTH('G Codes'!D6:D300)=MONTH(E5))*('G Codes'! C6:C300=C103)*('G Codes'!J6:J300)) sums the amounts in column J of 'G Codes' if the month on the 'G Codes' worksheet matches the on in E5 and the text in column C matches the text in C103 Now the strange thing is that for another worksheet the exact same formula works out fine. For the 'G Codes' worksheet it produces the #VALUE! error. The formulae are looking in the right place- there are no extra columns in this worksheet. I've checked the date and number columns for 'spaces' (ie text entries), and checked that the date formats are correct. I believe that there's a different way of writing the formula if you're exclusively using numbers but I need my formula to compare text. Mainly I don't understand why this works fine for one worksheet and not the other, and also that all the cells referring to the G Codes in this worksheet show #VALUE! (although in another worksheet they are referred to without any problems)... Can anyone help? Many thanks Chris |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT #VALUE! error
On Jun 19, 2:38 pm, xlbo wrote:
I would suggest that a piece of data in G Codes!D6:D300 cannot be converted to a "Month" value You say that you have checked Date FORMATS - have you checked to see whwther you have "real" dates or textual representations of dates ? -- Rgds, Geoff "A crash reduces Your expensive computer To a simple stone" "Chris Slowe" wrote: Hello. this formula =SUMPRODUCT((MONTH('G Codes'!D6:D300)=MONTH(E5))*('G Codes'! C6:C300=C103)*('G Codes'!J6:J300)) sums the amounts in column J of 'G Codes' if the month on the 'G Codes' worksheet matches the on in E5 and the text in column C matches the text in C103 Now the strange thing is that for another worksheet the exact same formula works out fine. For the 'G Codes' worksheet it produces the #VALUE! error. The formulae are looking in the right place- there are no extra columns in this worksheet. I've checked the date and number columns for 'spaces' (ie text entries), and checked that the date formats are correct. I believe that there's a different way of writing the formula if you're exclusively using numbers but I need my formula to compare text. Mainly I don't understand why this works fine for one worksheet and not the other, and also that all the cells referring to the G Codes in this worksheet show #VALUE! (although in another worksheet they are referred to without any problems)... Can anyone help? Many thanks Chris You're spot on! I had gone through the dates, and even put a validation check on them but somehow both I and the machine managed to miss an extra digit in one of the dates that was breaking everything. sorted now i think.. Thanks very much! Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct error | Excel Worksheet Functions | |||
#ref error with Sumproduct | Excel Worksheet Functions | |||
Sumproduct error | Excel Worksheet Functions | |||
SUMPRODUCT ERROR | Excel Discussion (Misc queries) | |||
Sumproduct #num error | Excel Worksheet Functions |