adding a 3rd criteria to an IF formula

  • Thread starter Thread starter GD
  • Start date Start date
G

GD

Hi, just reffirming my request for help in adding a 3rd criteria to the below
formula, can't for the life of me get it sorted! cheers

=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'!P3:P3000)-LEN(SUBSTITUTE('2008
Errors'!P3:P3000,"KT",""))>0),1)))

I'm looking to add a 3rd criteria dependand on a further range of cells
('2008 Errors'!B3:B3000) with the
text "Fax" occuring in them (and in the month of January in A3-A3000 and
"KT" in P3:P3000 as detailed in the formula above) would then produce a
cumulative result as the obove formula does for the two criteria...getting
mixed up with my brackets etc
Cheers
 
Sometimes when I get stuck, I edit the formula in NotePad, and use lots of
lines, line breaks, and initial spaces to display the formula. I'll start
with the outermost part like this:

=SUM(
)

then I'll insert the next part:

=SUM(
IF(condition,
if_true,
if_false
)
)

and so on. When it all seems logical and correct, I remove the spaces and
line feeds, and paste it into the cell.

I've seen at least a couple of formula editors on various web sites (check
Dick Kusleika's blog, Daily Dose of Excel), but none have seemed easier to
me than NotePad.

- Jon
 
Perhpas you could use the conditional sum option under tools, you can input a
large number of confitions over and above 3?
 
Well i've only got limited access to other websites as at work, but the
notepad suggestion has born some success...

=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'!P3:P3000)-LEN(SUBSTITUTE('2008 Errors'!P3:P3000,"Kirkby
Thore","")*(LEN('2008 Errors'!B3:B3000)-LEN(SUBSTITUTE('2008
Errors'!B3:B3000,"Fax",""))))>0),1)))

....however this now returns the result #VALUE! - which is a step forward
from earlier efforts just being reported as having an error in the formula!
 
You can also use the Evaluate Formula button on the Formula Auditing toolbar
to help figure out where a formula gets its results. You can have it
calculate a bit of the formula at a time, so you can see where you've made
an error.

- Jon
 
Back
Top