Quote:
Originally Posted by trevorc
Is there a way to use a named range from within the table in place of the range?
|
It is! Depending on what you mean by "named range from within the table":
1. When you mean Named Ranges created from Formulas>Name Manager, then
Code:
=COUNTIFS(Range1, "<>" & "", Range2,"Tablet")
Where Range1 and Range2 are your Named Ranges (Replace names with your own).
The best way to do this is to create a dynamic named range for your table on sheet Repairs. Like named range nRepairs defined as
Code:
=OFFSET(Repairs!$A$5,1,,COUNTA($A:$A)-1,20)
To have this to work, you must have headers for table in OFFSE's anchor cell (Repairs!$A$5) row, no caps in OFFSET's anchor cell column's datarange, and no entries outside of datarange for this column unless you correct it replacing -1 with another value.
Having nRepairs defined, you can easily define any column of datarange as a Named Range - e.g.
Range1 = INDEX(nRepairs,,18)
Range2 = INDEX(nRepairs,,5)
2. When you mean table as Defined Table (Insert>Table), like a Defined Table tRepairs, then
Code:
=COUNTIFS(tRepairs[HeaderInColumnR],"<>" & "",tRepairs[HeaderInColumnE],"Tablet")
In case you use this formula in another Defined Table and you have there a column e.g. Devices with a row for every device type, and you want to count Tablet's from nRepairs with Column R not empty, and Devices determined in column E, the formula will be
Code:
=COUNTIFS(tRepairs[HeaderInColumnR],"<>" & "",tRepairs[HeaderInColumnE],[@Devices])