#1
|
|||
|
|||
Count of non-blank cells in one row and specified content in another
Hi All,
I'm trying to get this countif to give me a count of cells that are not blank in one row and contain 'Tablet' in the other, both rows are located on another sheet. =COUNTIF('Repairs'!R6:R5000,"<>" + 'Repairs'!e6:e5000,"Tablet") And is there a way to make this dynamic in a table row? can i use the row named range? Any help would be great, even a point in the right direction regards Trevor |
#2
|
|||
|
|||
Code:
=COUNTIFS('Repairs'!$R$6:$R$5000,"<>" & "",'Repairs'!$E$6:$E$5000,"Tablet") COUNTIF() counts entries in a range matching defined condition for single column. COUNTIFS() counts entries in a range matching defined condition(s) for single or several columns. SUMIF() calculates SUM() of entries in single column in range matching a defined condition for another column. SUMIFS() calculates SUM() of entries in single column in range matching a defined condition(s) for single or several columns. |
#3
|
|||
|
|||
Thanks Arvi,
Works just fine. Is there a way to use a named range from within the table in place of the range? regards Trevor |
#4
|
|||
|
|||
Quote:
1. When you mean Named Ranges created from Formulas>Name Manager, then Code:
=COUNTIFS(Range1, "<>" & "", Range2,"Tablet") 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) 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") Code:
=COUNTIFS(tRepairs[HeaderInColumnR],"<>" & "",tRepairs[HeaderInColumnE],[@Devices]) |
#5
|
|||
|
|||
WOW, that's awsum.
Appreciate the assistance regards Trevor |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Trying to count the number of cells ending with 001 or 002 | mgroessl | Excel Programming | 5 | 08-06-2019 03:39 PM |
Distribute text in one cell across a range of cells (overcoming selection.range.cells.count bug) | slaycock | Word VBA | 0 | 02-18-2017 07:00 AM |
Count if another cell is blank | caz46 | Excel | 4 | 11-02-2015 07:25 AM |
How to Count from Different Cells? | Shahzad | Excel | 3 | 06-26-2010 01:06 AM |
Count range cells eliminating merge cells | danbenedek | Excel | 0 | 06-15-2010 12:40 AM |