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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"GD" wrote in message
...
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

Perhpas you could use the conditional sum option under tools, you can input a
large number of confitions over and above 3?

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"GD" wrote in message
...
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

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!

"Neil Pearce" wrote:

Perhpas you could use the conditional sum option under tools, you can input a
large number of confitions over and above 3?

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"GD" wrote in message
...
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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"GD" wrote in message
...
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!

"Neil Pearce" wrote:

Perhpas you could use the conditional sum option under tools, you can
input a
large number of confitions over and above 3?

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"GD" wrote in message
...
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