How do I do simultaneous AND and OR filtering ?

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Glad that worked for you. Thanks for the feedback!