Stumped

[GD]

Ok, as it's an ongoing thing i'm still really stumped! Ive tried the formula
evaluation which says it's fine, ive broken it down in notepad and it looks
fine, ive made sure the criteria are identical to how the text appear in the
source cells....but the following formula just won't work!
=SUM(IF(MOD(ROW('2008 Errors'!$A$3:$A$3000),10)=3,IF((YEAR('2008
Errors'!$A$3:$A$3000)=2008)*(MONTH('2008 Errors'!$A$3:$A$3000)=1)*(LEN('2008
Errors'!P33000)-LEN(SUBSTITUTE('2008 Errors'!P33000,"Kirkby
Thore","")*(LEN('2008 Errors'!B3:B3000)-LEN(SUBSTITUTE('2008
Errors'!B3:B3000,"Phone",""))))>0),1)))

I'm utterly stumped as it appears fine, but is producing #VALUE! - am i
being daft and missing something in Excel where three criteria is done in
another way? I'm just looking for IF (cells are January)&(cells say "Kirkby
Thore")&(cells say Fax)=1..?
Any ideas?

 

[Ron Rosenfeld]

Ok, as it's an ongoing thing i'm still really stumped! Ive tried the formula
evaluation which says it's fine, ive broken it down in notepad and it looks
fine, ive made sure the criteria are identical to how the text appear in the
source cells....but the following formula just won't work!
=SUM(IF(MOD(ROW('2008 Errors'!$A$3:$A$3000),10)=3,IF((YEAR('2008
Errors'!$A$3:$A$3000)=2008)*(MONTH('2008 Errors'!$A$3:$A$3000)=1)*(LEN('2008
Errors'!P33000)-LEN(SUBSTITUTE('2008 Errors'!P33000,"Kirkby
Thore","")*(LEN('2008 Errors'!B3:B3000)-LEN(SUBSTITUTE('2008
Errors'!B3:B3000,"Phone",""))))>0),1)))

I'm utterly stumped as it appears fine, but is producing #VALUE! - am i
being daft and missing something in Excel where three criteria is done in
another way? I'm just looking for IF (cells are January)&(cells say "Kirkby
Thore")&(cells say Fax)=1..?
Any ideas?

Not trying to analyze the formula in detail, but:

Since this is an array formula, you must enter it by holding down <ctrl><shift>
while hitting <enter>. If you did it correctly, XL will place braces {...}
around the formula.
--ron

 

[David Biddulph]

Are you sure that you've checked your parentheses properly?

I've broken it down by lines to try to make things clearer, and I think you
probably wanted:
=SUM(
IF(
MOD(ROW('2008 Errors'!$A$3:$A$3000),10)=3,
IF(
(YEAR('2008 Errors'!$A$3:$A$3000)=2008)*(MONTH('2008
Errors'!$A$3:$A$3000)=1)
*(LEN('2008 Errors'!P33000)-LEN(SUBSTITUTE('2008 Errors'!P33000,"Kirkby
Thore","")))
*(LEN('2008 Errors'!B3:B3000)-LEN(SUBSTITUTE('2008
Errors'!B3:B3000,"Phone","")))

 

[GD]

Finally! I mustn't have because that now works! Thank's ever so much for
that, i've been struggling on and off for a week trying to get it sorted -
having a sit and look at it stage by stage now to see where i'd gone wrong -
but thanks, I can finally get this finished now

Cheers

 

[David Biddulph]

All I can suggest in such situations (when you've got a long formula
misbehaving) is to break it down into manageable chunks and see how each
part behaves separately.
Excel can help a little when it colour-codes the parentheses to give you a
chance of sorting them out, but sometimes you need to do need to go through
the process of splitting chunks onto separate lines to enable yourself to
see the structure.

[But anyway, having been born and brought up in Westmorland, I couldn't
ignore a formula including the string "Kirkby Thore"!]