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

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

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