Help with Formula Please

[PlutoNash]

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.

 

[Fred Smith]

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

 

[cm]

adjust your ranges to fit for columns F and I:

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

 

[PlutoNash]

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

 

[PlutoNash]

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]

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)?

 

[Fred Smith]

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]

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]

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]

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. )

 

[Fred Smith]

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]

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