Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 02-22-2016, 01:01 PM
dayjef dayjef is offline Counting Windows 8 Counting Office 2000
Novice
Counting
 
Join Date: Feb 2016
Posts: 2
dayjef is on a distinguished road
Default Counting

I have a range of cells that contain the letter "W" and are then followed by a number. I would like to count the cells in that range containing the "W" regardless of what may follow it.
Reply With Quote
  #2  
Old 02-22-2016, 07:05 PM
macropod's Avatar
macropod macropod is offline Counting Windows 7 64bit Counting Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You could use an array formula like:
=SUM(IF(LEFT(A:A,1)="W",1))
Ideally, though, you'd restrict the range to less than an entire row or column (e.g. instead of A:A you might use A1:A100).

Array formulae are inserted with Ctrl-Shift-Enter.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 02-23-2016, 01:18 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Counting Windows 7 64bit Counting Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Or eventually =SUMPRODUCT(--(LEFT(A1:A100,1)="W"))
which does not need entering with CSE

( if you use Office 2000 entire ranges will not work for sumproduct)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 02-23-2016, 01:57 AM
xor xor is offline Counting Windows 10 Counting Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Or what about a non-array formula or non-array like formula (as SUMPRODUCT)

=COUNTIF(A1:A100,"w*")
Reply With Quote
  #5  
Old 02-23-2016, 03:00 PM
dayjef dayjef is offline Counting Windows 8 Counting Office 2000
Novice
Counting
 
Join Date: Feb 2016
Posts: 2
dayjef is on a distinguished road
Default

AWESOME, this works as desired. THANK YOU so much!!!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting unique visitors by ward, counting monthly visits by status, editing existing workbook JaxV Excel 9 11-14-2014 12:25 AM
counting colours not_dave Excel 1 12-29-2013 01:27 AM
Counting various data needs counting apples Excel 2 09-16-2012 04:52 AM
Counting Counting Formula Karen615 Excel 6 06-20-2011 07:19 PM
Counting Counting Colors g48dd Excel 2 03-13-2011 09:28 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:23 PM.


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