Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-20-2021, 09:15 PM
trevorc trevorc is offline Count of non-blank cells in one row and specified content in another Windows 7 32bit Count of non-blank cells in one row and specified content in another Office 2013
Competent Performer
Count of non-blank cells in one row and specified content in another
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default 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
Reply With Quote
  #2  
Old 04-20-2021, 10:48 PM
ArviLaanemets ArviLaanemets is offline Count of non-blank cells in one row and specified content in another Windows 8 Count of non-blank cells in one row and specified content in another Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Code:
=COUNTIFS('Repairs'!$R$6:$R$5000,"<>" & "",'Repairs'!$E$6:$E$5000,"Tablet")
Btw. As COUNTIFS() and SUMIFS() work with a single condition too, I simply use only them always instead COUNTIF() and SUMIF() - so whenever I need to use several conditions instead of one, I simply can add conditions instead replacing entire function.

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.
Reply With Quote
  #3  
Old 04-21-2021, 12:13 PM
trevorc trevorc is offline Count of non-blank cells in one row and specified content in another Windows 7 32bit Count of non-blank cells in one row and specified content in another Office 2013
Competent Performer
Count of non-blank cells in one row and specified content in another
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

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
Reply With Quote
  #4  
Old 04-21-2021, 11:54 PM
ArviLaanemets ArviLaanemets is offline Count of non-blank cells in one row and specified content in another Windows 8 Count of non-blank cells in one row and specified content in another Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by trevorc View Post
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])
Reply With Quote
  #5  
Old 04-22-2021, 11:50 AM
trevorc trevorc is offline Count of non-blank cells in one row and specified content in another Windows 7 32bit Count of non-blank cells in one row and specified content in another Office 2013
Competent Performer
Count of non-blank cells in one row and specified content in another
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

WOW, that's awsum.
Appreciate the assistance
regards
Trevor
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count of non-blank cells in one row and specified content in another 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 of non-blank cells in one row and specified content in another 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:16 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft