Help with Formula Please
 

I need to count up everytime a Y appears in a cell in column I ('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This is one of the
many variations I have tried. The trouble is, it counts ALL the Y's in column
I instead of only those where a 3 is contained in a cell in column F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"), COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

Help with Formula Please
 

Countif supports only one "if". If you have Excel 2007, you can use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the range to suit.

Regards,
Fred

"PlutoNash" wrote in message
...
I need to count up everytime a Y appears in a cell in column I ('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This is one of
the
many variations I have tried. The trouble is, it counts ALL the Y's in
column
I instead of only those where a 3 is contained in a cell in column F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"), COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

Help with Formula Please
 

adjust your ranges to fit for columns F and I:

=SUMPRODUCT((F1:F6=3)*(I1:I6="Y"))


--
hope to help,
cm


"PlutoNash" wrote:

I need to count up everytime a Y appears in a cell in column I ('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This is one of the
many variations I have tried. The trouble is, it counts ALL the Y's in column
I instead of only those where a 3 is contained in a cell in column F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"), COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

Help with Formula Please
 

cm and Fred Smith, thank you both for taking the time to help me...works just
fine.

I have applied the formula to the rest of the cells, changing the 3 to the
relevant week number for the cell. The problem now is that future weeks have
no data to find (due to it not yet being entered in 'Data Sheet') and returns
a zero. Obviously, the line on the line chart drops to zero for future dates.
I want the line to stop at the last value (this may, some weeks, also include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the range to suit.

Regards,
Fred

"PlutoNash" wrote in message
...
I need to count up everytime a Y appears in a cell in column I ('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This is one of
the
many variations I have tried. The trouble is, it counts ALL the Y's in
column
I instead of only those where a 3 is contained in a cell in column F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"), COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.

Help with Formula Please
 

I have tried the following formula but, while it removes the zeros, the line
is still plotted and drops below zero for future dates.


=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,"",SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))


"PlutoNash" wrote:

cm and Fred Smith, thank you both for taking the time to help me...works just
fine.

I have applied the formula to the rest of the cells, changing the 3 to the
relevant week number for the cell. The problem now is that future weeks have
no data to find (due to it not yet being entered in 'Data Sheet') and returns
a zero. Obviously, the line on the line chart drops to zero for future dates.
I want the line to stop at the last value (this may, some weeks, also include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the range to suit.

Regards,
Fred

"PlutoNash" wrote in message
...
I need to count up everytime a Y appears in a cell in column I ('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This is one of
the
many variations I have tried. The trouble is, it counts ALL the Y's in
column
I instead of only those where a 3 is contained in a cell in column F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"), COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.

Help with Formula Please
 

I have also tried replacing .....=0,"",...... with ....=0,NA(),..... but it
breaks all cells, even where the cell contains a number 0.

Is there some way of stopping the formula you gave me returning a result if
it can't find the week number (3 in the original formula)?

"PlutoNash" wrote:

I have tried the following formula but, while it removes the zeros, the line
is still plotted and drops below zero for future dates.


=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,"",SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))


"PlutoNash" wrote:

cm and Fred Smith, thank you both for taking the time to help me...works just
fine.

I have applied the formula to the rest of the cells, changing the 3 to the
relevant week number for the cell. The problem now is that future weeks have
no data to find (due to it not yet being entered in 'Data Sheet') and returns
a zero. Obviously, the line on the line chart drops to zero for future dates.
I want the line to stop at the last value (this may, some weeks, also include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the range to suit.

Regards,
Fred

"PlutoNash" wrote in message
...
I need to count up everytime a Y appears in a cell in column I ('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This is one of
the
many variations I have tried. The trouble is, it counts ALL the Y's in
column
I instead of only those where a 3 is contained in a cell in column F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"), COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.

Help with Formula Please
 

To avoid an entry from being plotted, set it to #N/A, rather than 0, as in:
=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

Regards,
Fred


"PlutoNash" wrote in message
...
I have also tried replacing .....=0,"",...... with ....=0,NA(),..... but it
breaks all cells, even where the cell contains a number 0.

Is there some way of stopping the formula you gave me returning a result
if
it can't find the week number (3 in the original formula)?

"PlutoNash" wrote:

I have tried the following formula but, while it removes the zeros, the
line
is still plotted and drops below zero for future dates.


=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,"",SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))


"PlutoNash" wrote:

cm and Fred Smith, thank you both for taking the time to help
me...works just
fine.

I have applied the formula to the rest of the cells, changing the 3 to
the
relevant week number for the cell. The problem now is that future weeks
have
no data to find (due to it not yet being entered in 'Data Sheet') and
returns
a zero. Obviously, the line on the line chart drops to zero for future
dates.
I want the line to stop at the last value (this may, some weeks, also
include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the range to
suit.

Regards,
Fred

"PlutoNash" wrote in message
...
I need to count up everytime a Y appears in a cell in column I
('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This is
one of
the
many variations I have tried. The trouble is, it counts ALL the Y's
in
column
I instead of only those where a 3 is contained in a cell in column
F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"),
COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.

Help with Formula Please
 

Thanks Fred,

The problem I have is I need to plot zeros for current and past dates but
not for future dates.

Do you think this should work?

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")),"")

The first SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8 being used to check for
the presence of the week number. If the cell range does not contain the week
number I am looking for, then the formula returns "", leaving future dates
blank.

However, if it is found, the

SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y"))

part of the formula runs.

What do you think?

Many thanks

"Fred Smith" wrote:

To avoid an entry from being plotted, set it to #N/A, rather than 0, as in:
=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

Regards,
Fred


"PlutoNash" wrote in message
...
I have also tried replacing .....=0,"",...... with ....=0,NA(),..... but it
breaks all cells, even where the cell contains a number 0.

Is there some way of stopping the formula you gave me returning a result
if
it can't find the week number (3 in the original formula)?

"PlutoNash" wrote:

I have tried the following formula but, while it removes the zeros, the
line
is still plotted and drops below zero for future dates.


=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,"",SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))


"PlutoNash" wrote:

cm and Fred Smith, thank you both for taking the time to help
me...works just
fine.

I have applied the formula to the rest of the cells, changing the 3 to
the
relevant week number for the cell. The problem now is that future weeks
have
no data to find (due to it not yet being entered in 'Data Sheet') and
returns
a zero. Obviously, the line on the line chart drops to zero for future
dates.
I want the line to stop at the last value (this may, some weeks, also
include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the range to
suit.

Regards,
Fred

"PlutoNash" wrote in message
...
I need to count up everytime a Y appears in a cell in column I
('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This is
one of
the
many variations I have tried. The trouble is, it counts ALL the Y's
in
column
I instead of only those where a 3 is contained in a cell in column
F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"),
COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.


.

Help with Formula Please
 

I don't know the layout of your data, so can't say for sure whether your
format will work. Try it and see. My one comment is that blanks will still
be plotted. It's only #N/A which Excel will ignore in a graph.

Regards,
Fred

"PlutoNash" wrote in message
...
Thanks Fred,

The problem I have is I need to plot zeros for current and past dates but
not for future dates.

Do you think this should work?

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")),"")

The first SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8 being used to check
for
the presence of the week number. If the cell range does not contain the
week
number I am looking for, then the formula returns "", leaving future dates
blank.

However, if it is found, the

SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))

part of the formula runs.

What do you think?

Many thanks

"Fred Smith" wrote:

To avoid an entry from being plotted, set it to #N/A, rather than 0, as
in:
=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

Regards,
Fred


"PlutoNash" wrote in message
...
I have also tried replacing .....=0,"",...... with ....=0,NA(),..... but
it
breaks all cells, even where the cell contains a number 0.

Is there some way of stopping the formula you gave me returning a
result
if
it can't find the week number (3 in the original formula)?

"PlutoNash" wrote:

I have tried the following formula but, while it removes the zeros,
the
line
is still plotted and drops below zero for future dates.


=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,"",SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))


"PlutoNash" wrote:

cm and Fred Smith, thank you both for taking the time to help
me...works just
fine.

I have applied the formula to the rest of the cells, changing the 3
to
the
relevant week number for the cell. The problem now is that future
weeks
have
no data to find (due to it not yet being entered in 'Data Sheet')
and
returns
a zero. Obviously, the line on the line chart drops to zero for
future
dates.
I want the line to stop at the last value (this may, some weeks,
also
include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can
use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the range
to
suit.

Regards,
Fred

"PlutoNash" wrote in message
...
I need to count up everytime a Y appears in a cell in column I
('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This
is
one of
the
many variations I have tried. The trouble is, it counts ALL the
Y's
in
column
I instead of only those where a 3 is contained in a cell in
column
F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"),
COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.


.

Help with Formula Please
 

Thanks for your help Fred.

I tried the following:

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500="")),#N/A,SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

It works for current & past dates but not for future dates (blank cells). It
enters a zero instead of nothing. Oh well...I'll try again. :o)


"Fred Smith" wrote:

I don't know the layout of your data, so can't say for sure whether your
format will work. Try it and see. My one comment is that blanks will still
be plotted. It's only #N/A which Excel will ignore in a graph.

Regards,
Fred

"PlutoNash" wrote in message
...
Thanks Fred,

The problem I have is I need to plot zeros for current and past dates but
not for future dates.

Do you think this should work?

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")),"")

The first SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8 being used to check
for
the presence of the week number. If the cell range does not contain the
week
number I am looking for, then the formula returns "", leaving future dates
blank.

However, if it is found, the

SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))

part of the formula runs.

What do you think?

Many thanks

"Fred Smith" wrote:

To avoid an entry from being plotted, set it to #N/A, rather than 0, as
in:
=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

Regards,
Fred


"PlutoNash" wrote in message
...
I have also tried replacing .....=0,"",...... with ....=0,NA(),..... but
it
breaks all cells, even where the cell contains a number 0.

Is there some way of stopping the formula you gave me returning a
result
if
it can't find the week number (3 in the original formula)?

"PlutoNash" wrote:

I have tried the following formula but, while it removes the zeros,
the
line
is still plotted and drops below zero for future dates.


=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,"",SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))


"PlutoNash" wrote:

cm and Fred Smith, thank you both for taking the time to help
me...works just
fine.

I have applied the formula to the rest of the cells, changing the 3
to
the
relevant week number for the cell. The problem now is that future
weeks
have
no data to find (due to it not yet being entered in 'Data Sheet')
and
returns
a zero. Obviously, the line on the line chart drops to zero for
future
dates.
I want the line to stop at the last value (this may, some weeks,
also
include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can
use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the range
to
suit.

Regards,
Fred

"PlutoNash" wrote in message
...
I need to count up everytime a Y appears in a cell in column I
('Data
Sheet'!I:I) but only when a cell contains a 3 in column F. This
is
one of
the
many variations I have tried. The trouble is, it counts ALL the
Y's
in
column
I instead of only those where a 3 is contained in a cell in
column
F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"),
COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.


.


.

Help with Formula Please
 

Try using the recommendation that was given to you. You need to use the NA
function, not the characters #N/A. Try the following:
=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500="")),NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

However, I'm not clear on what you are testing for in the If statement, so
you may need to check that.

Regards,
Fred

"PlutoNash" wrote in message
...
Thanks for your help Fred.

I tried the following:

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500="")),#N/A,SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

It works for current & past dates but not for future dates (blank cells).
It
enters a zero instead of nothing. Oh well...I'll try again. :o)


"Fred Smith" wrote:

I don't know the layout of your data, so can't say for sure whether your
format will work. Try it and see. My one comment is that blanks will
still
be plotted. It's only #N/A which Excel will ignore in a graph.

Regards,
Fred

"PlutoNash" wrote in message
...
Thanks Fred,

The problem I have is I need to plot zeros for current and past dates
but
not for future dates.

Do you think this should work?

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")),"")

The first SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8 being used to
check
for
the presence of the week number. If the cell range does not contain the
week
number I am looking for, then the formula returns "", leaving future
dates
blank.

However, if it is found, the

SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))

part of the formula runs.

What do you think?

Many thanks

"Fred Smith" wrote:

To avoid an entry from being plotted, set it to #N/A, rather than 0,
as
in:
=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

Regards,
Fred


"PlutoNash" wrote in message
...
I have also tried replacing .....=0,"",...... with ....=0,NA(),.....
but
it
breaks all cells, even where the cell contains a number 0.

Is there some way of stopping the formula you gave me returning a
result
if
it can't find the week number (3 in the original formula)?

"PlutoNash" wrote:

I have tried the following formula but, while it removes the zeros,
the
line
is still plotted and drops below zero for future dates.


=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,"",SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))


"PlutoNash" wrote:

cm and Fred Smith, thank you both for taking the time to help
me...works just
fine.

I have applied the formula to the rest of the cells, changing the
3
to
the
relevant week number for the cell. The problem now is that future
weeks
have
no data to find (due to it not yet being entered in 'Data Sheet')
and
returns
a zero. Obviously, the line on the line chart drops to zero for
future
dates.
I want the line to stop at the last value (this may, some weeks,
also
include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can
use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data
Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the
range
to
suit.

Regards,
Fred

"PlutoNash" wrote in
message
...
I need to count up everytime a Y appears in a cell in column I
('Data
Sheet'!I:I) but only when a cell contains a 3 in column F.
This
is
one of
the
many variations I have tried. The trouble is, it counts ALL
the
Y's
in
column
I instead of only those where a 3 is contained in a cell in
column
F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"),
COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.


.


.

Help with Formula Please
 

Of the formula:

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500="")),NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

the first part - SUMPRODUCT(('Data Sheet'!$F$4:$F$65500="")), is meant to
check if the cell/s in column F of Data Sheet is blank/empty.

If it is the NA() should leave the destination cell (the one with the
formula) should be left blank/empty so it won't be plotted.

The final part you know about...

For some reason where I would expect the IF statement to stop at NA(),
actually returns a 0 (zero).

Regards

PlutoNash

"Fred Smith" wrote:

Try using the recommendation that was given to you. You need to use the NA
function, not the characters #N/A. Try the following:
=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500="")),NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

However, I'm not clear on what you are testing for in the If statement, so
you may need to check that.

Regards,
Fred

"PlutoNash" wrote in message
...
Thanks for your help Fred.

I tried the following:

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500="")),#N/A,SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

It works for current & past dates but not for future dates (blank cells).
It
enters a zero instead of nothing. Oh well...I'll try again. :o)


"Fred Smith" wrote:

I don't know the layout of your data, so can't say for sure whether your
format will work. Try it and see. My one comment is that blanks will
still
be plotted. It's only #N/A which Excel will ignore in a graph.

Regards,
Fred

"PlutoNash" wrote in message
...
Thanks Fred,

The problem I have is I need to plot zeros for current and past dates
but
not for future dates.

Do you think this should work?

=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")),"")

The first SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8 being used to
check
for
the presence of the week number. If the cell range does not contain the
week
number I am looking for, then the formula returns "", leaving future
dates
blank.

However, if it is found, the

SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))

part of the formula runs.

What do you think?

Many thanks

"Fred Smith" wrote:

To avoid an entry from being plotted, set it to #N/A, rather than 0,
as
in:
=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,NA(),SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))

Regards,
Fred


"PlutoNash" wrote in message
...
I have also tried replacing .....=0,"",...... with ....=0,NA(),.....
but
it
breaks all cells, even where the cell contains a number 0.

Is there some way of stopping the formula you gave me returning a
result
if
it can't find the week number (3 in the original formula)?

"PlutoNash" wrote:

I have tried the following formula but, while it removes the zeros,
the
line
is still plotted and drops below zero for future dates.


=IF(SUMPRODUCT(('Data Sheet'!$F$4:$F$65500=$G8)*('Data
Sheet'!I$4:I$65500="Y"))=0,"",SUMPRODUCT(('Data
Sheet'!$F$4:$F$65500=$G8)*('Data Sheet'!I$4:I$65500="Y")))


"PlutoNash" wrote:

cm and Fred Smith, thank you both for taking the time to help
me...works just
fine.

I have applied the formula to the rest of the cells, changing the
3
to
the
relevant week number for the cell. The problem now is that future
weeks
have
no data to find (due to it not yet being entered in 'Data Sheet')
and
returns
a zero. Obviously, the line on the line chart drops to zero for
future
dates.
I want the line to stop at the last value (this may, some weeks,
also
include
a zero value). Is there some way of dealing with this issue?

Many thanks

"Fred Smith" wrote:

Countif supports only one "if". If you have Excel 2007, you can
use
Countifs. If not, use Sumproduct, as in:
=sumproduct(('Data Sheet'!f1:f1000=3)*('Data
Sheet'!i1:i1000="Y"))

You cannot use full columns with Sumproduct, so adjust the
range
to
suit.

Regards,
Fred

"PlutoNash" wrote in
message
...
I need to count up everytime a Y appears in a cell in column I
('Data
Sheet'!I:I) but only when a cell contains a 3 in column F.
This
is
one of
the
many variations I have tried. The trouble is, it counts ALL
the
Y's
in
column
I instead of only those where a 3 is contained in a cell in
column
F.

=IF(AND('Data Sheet'!F4=3,'Data Sheet'!I3="Porosity"),
COUNTIF('Data
Sheet'!I:I,"Y"))

I would really appreciate some help for a newbie if possible.

ATB.

.


.


.


.