Dear JMB:
Thanks, that worked fabulously! I checked for distortions in the column
where I had the results of a SUMIF/(COUNTIF copied and dragged down the
entire worksheet as far as there was data, and the numbers were identical in
the filtered and non-filtered worksheets. If the range field had been broken
up, and XL had recalculated, there surely would have been variation, which
there wasn't.
Thanks again for your help!
Mick
"JMB" wrote:
> You're pretty close:
> =AND (A2>0, B2>0, C2>0, OR(D2>0, E2>0, F2>0))
>
> Recalculation should not affect your data, the rows are only hidden so your
> formula references won't change (unless your formulae use Subtotal, which
> excludes rows hidden by a filter). When you paste the filtered data to a new
> sheet, my experience has always been XL will paste the values, not the
> formulae - so your pasted data will be a "snapshot".
>
>
> "Motown Mick" wrote:
>
> > I would like to do a filtering operation that is a combination of an AND
> > command and an OR command. In other words, I have a worksheet consisting of
> > data in say, columns A, B, and C that I am requiring to be greater than zero.
> > In another set of columns in the worksheet, say columns D, E, and F, I am
> > requiring AT LEAST ONE of the entries to be greater than zero. So
> > intuitively, the command structure would be something like =AND (A2>0, B2>0,
> > C2>0) OR(D2>0, E2>0, F2>0), but I am not sure of the exact command syntax one
> > would need to do the two things simultaneously.
> >
> > I plan to do this in a column with the heading “FILTER”. It is my hope that
> > by writing in the correct command structure, and filtering for “TRUE”, I will
> > be able to filter for all the rows that are completely free of non-zero
> > entries in A, B, and C, and have at least one entry greater than zero in D,
> > E, or F. Can anybody suggest the correct way to proceed?
> >
> > I plan to copy these filtered results into another worksheet. One thing I
> > would like to know about filtering is when Excel does this, is it just like
> > taking a photograph of the rows you have specified in the worksheet as they
> > are, or does Excel do any formula recalculation based on the fractured and
> > fragmented worksheet that now appears? In other words, this worksheet has a
> > lot of operations I’ve performed on it that are commands based on a range
> > reference, such as VLOOKUP, SUMIF/(COUNTIF, and IF(ISNA(MATCH. I hope that
> > when I do the filtering, Excel does not recalculate the values based on the
> > fractured and fragmented ranges that appear "ex post facto" after the
> > filtering has taken place. That would distort the results, and lead to
> > errors and misinformation. Are there any precautions I must take to ensure
> > that when I do the filtering, Excel copies the values as they are, and does
> > not make any formula recalculations based on the fractured and fragmented
> > range designations?
> >
> > Mick
> >
Similar Threads
