Excel: How do I filter records based on criteria across different cells?

January 19, 2018 10:11 AM

Excel experts: How can I filter this range so that I only pull records with red cells (these were originally blank)? This is a pivot table that I converted back into a range. Alternatively, is there a way I can filter my original pivot table so that it only displays records with blank entries? Thanks in advance!
posted by matticulate to Computers & Internet (11 answers total)

Best answer: To clarify - you want to limit this table to show rows that have any blank columns? Is that right?
posted by brainmouse at 10:19 AM on January 19, 2018

Best answer: Can you create a helper column that calculates if a record has blanks using isblank() and ifs statements? Then you can filter on that. Not the most elegant, but it should work.
posted by MadamM at 10:19 AM on January 19, 2018 [2 favorites]

In your original pivot table, you can filter the field you dropped into your "values" section to show only blanks.
posted by millipede at 10:20 AM on January 19, 2018

I agree that you should use a helper column but you don't need to use a complicated formula - there's a =countblank() formula that will return the number of blanks in a range.
posted by brainmouse at 10:22 AM on January 19, 2018 [1 favorite]

Could you use filter by color?
posted by zamboni at 10:27 AM on January 19, 2018

Best answer: @brainmouse - Yep. I want to pull only those records that have a blank cell in one or more columns.

@MadamM - Thanks for this suggestion - I just did this and it worked like a charm!

This was the winning formula:

=IF(OR(ISBLANK(H2),ISBLANK(I2),ISBLANK(J2),ISBLANK(K2),ISBLANK(L2),ISBLANK(O2),ISBLANK(P2),ISBLANK(Q2),ISBLANK(R2)),"Fix","OK")

Thanks again, everybody!
posted by matticulate at 10:49 AM on January 19, 2018 [1 favorite]

Best answer: You could have done this directly in the pivot table using filters, but a helper column is best practice. Another way to approach in the future is use COUNTA() which counts non-empty cells in one or more ranges, and is a little bit less cumbersome.
posted by voiceofreason at 11:18 AM on January 19, 2018

Best answer: =IF(COUNTBLANKS(H2:L2)+COUNTBLANKS(O2:R2)>0,"Fix","OK") is more succinct than the solution you posted, if that matters.
posted by ambrosen at 11:21 AM on January 19, 2018 [1 favorite]

I believe that's COUNTBLANK(), not COUNTBLANKS().
posted by flabdablet at 1:31 AM on January 20, 2018

Best answer: Yeah, I actually ended up using the COUNTBLANK formula you guys suggested:

=SUM(COUNTBLANK(H2:L2),COUNTBLANK(O2:R2))

(I needed to omit blank cells from certain columns)

This is actually more useful than my original (and more convoluted) method, because this displays the total number of blanks for each record.

Thanks to everyone for your help!
posted by matticulate at 4:28 PM on January 23, 2018 [1 favorite]

=COUNTBLANK(H2:L2)+COUNTBLANK(O2:R2) does the same thing and is a tad easier to read.
posted by flabdablet at 4:23 PM on January 26, 2018