CODEWITHSUNDEEP
excelexcel-formula
user112802
user112802

Reputation: 23

Ignoring Hidden Table Rows in Excel Formula

Good Evening.

I am trying to get my formulas to ignore hidden rows in a filtered table. I have attempted some of the tricks shown here, but I haven't been successful.

The CSV I get from BigFix (network management tool) looks something like: [Table]

The applications column lists the applications in the same cell. As a result, when I do a count function to identify the issues, I have to use a wildcard (I'm searching for the results in a different tab).

enter image description here

=COUNTIF('Input Data'!C:C,"*"&Results!A2&"*")

I want to be able to filter the table on the first tab, and have the formula results show up accurately on the 2nd tab.

Any ideas?

UPDATE: I feel more information would help. The reason I want the "results" tab to update automatically is that I plan to build graphs out of the information on that tab. I would love to be able to filter the table in the 'input data' to include only Department A and have the graph autoupdate to Department A's info.

The excel files I'm working with have up to a thousand entries, and I'm trying to get graphs I can copy/paste to put into a presentation as efficiently as possible.

Upvotes: 2

Views: 3130

Answers (2)

Sixthsense
Sixthsense

Reputation: 1975

=SUMPRODUCT(SUBTOTAL(3,OFFSET('Input Data'!C:C,ROW('Input Data'!C:C)-MIN(ROW('Input Data'!C:C)),,1))*(ISNUMBER(SEARCH(A2,'Input Data'!C:C))))

In the above formula Restrict the C:C to your actual data range or use a named range to identify the Actual Data Range.

Edit

Above formula should be replaced with 103 instead of 3 as pointed by Jeep

=SUMPRODUCT(SUBTOTAL(103,OFFSET('Input Data'!C:C,ROW('Input Data'!C:C)-MIN(ROW('Input Data'!C:C)),,1))*(ISNUMBER(SEARCH(A2,'Input Data'!C:C))))

In the above formula Restrict the C:C to your actual data range or use a named range to identify the Actual Data Range.

Adding Explanation based on OP's Request.

In the above formula Used Sumproduct() Subtotal() Offset() Min() Row() IsNumber() Search() function combinations for arriving your expected result.

Row() - Will get the Row Number of a given range

Min() - Will get the Minimum Value of a given Numbers

Offset() - Is used to redirect the reference to each cell of a given range.

Subtotal() - Is used to find the (un)hidden state of redirected reference.

Search() - Is used to find the specific text in a given range (Not case sensitive) will result Number or error.

IsNumber() Is Used to check whether search returns Number or error. So Isnumber will return boolean True/False as result.

Column-C Column-D

Data For Filtering

a 1

b 1

a 1

a 2

a 2

The above data starts from 1st Row with headers in the 1st Row. Assume that I filtered D column With 1. Using the below formula will result 2.

=SUMPRODUCT(SUBTOTAL(103,OFFSET('Input Data'!C2:C6,ROW('Input Data'!C2:C6)-MIN(ROW('Input Data'!C2:C6)),,1))*(ISNUMBER(SEARCH("a",'Input Data'!C2:C6))))

ROW('Input Data'!C2:C6) = {2;3;4;5;6}

MIN(ROW('Input Data'!C2:C6)) = 2

ROW('Input Data'!C2:C6)-MIN(ROW('Input Data'!C2:C6)) should be read as {2;3;4;5;6}-2 = {0;1;2;3;4}

OFFSET('Input Data'!C2:C6,ROW('Input Data'!C2:C6)-MIN(ROW('Input Data'!C2:C6)),,1) = 'Input Data'!C2,'Input Data'!C3,'Input Data'!C4,'Input Data'!C5,'Input Data'!C6

SUBTOTAL(103,OFFSET('Input Data'!C2:C6,ROW('Input Data'!C2:C6)-MIN(ROW('Input Data'!C2:C6)),,1)) should be read as

SUBTOTAL(103,'Input Data'!C2,'Input Data'!C3,'Input Data'!C4,'Input Data'!C5,'Input Data'!C6) = {1;1;1;0;0} Subtotal() arrived the visible state of each (cell) reference.

SEARCH("a",'Input Data'!C2:C6) = {1;#VALUE!;1;1;1}

ISNUMBER(SEARCH("a",'Input Data'!C2:C6)) should be read as ISNUMBER({1;#VALUE!;1;1;1}) = {TRUE;FALSE;TRUE;TRUE;TRUE}

SUBTOTAL(103,OFFSET('Input Data'!C2:C6,ROW('Input Data'!C2:C6)-MIN(ROW('Input Data'!C2:C6)),,1)) = {1;1;1;0;0}

(ISNUMBER(SEARCH("a",'Input Data'!C2:C6))) = {TRUE;FALSE;TRUE;TRUE;TRUE}

{1;1;1;0;0}*{TRUE;FALSE;TRUE;TRUE;TRUE} = {1;0;1;0;0}

SUMPRODUCT({1;0;1;0;0}) = 2

Used Sumproduct() to avoid Array Entry and also to do the Array Formula Task (Looping through the range of cells) and Sumproduct() will result the Sum of the passed values.

Upvotes: 1

teylyn
teylyn

Reputation: 35915

Instead of filtering the source data, use the Countifs function and add the filter conditions to the Countifs.

Upvotes: 1

Related Questions