Counting the first character in a row

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
Good day all. I am running Excel out of Office365 (updated) on Windows 10 Home. I have a worksheet with eight Columns and right now 30 working Rows. The number of Rows may increase. Column A contains song titles. At the bottom of the worksheet I total the number of songs for reference using COUNTA. The worksheet is used to list elements of my internet music show and I would like to incorporate voice breaks into this worksheet. The COUNTA finds all cells that are not blank. I need to find a way to count Column A to show the number of actual songs and also the number of voice breaks. I tried using an Asterisk as the first character of a voice break, but Excel seems to confuse that as an arithmetic function and I understand that. that then means that I cannot use a Plus or Minus sign or a Division sign. I elected to use an Exclamation Point as the first character of the voice break. I have tried using the COUNTIF function, but I'm not having much luck with it. Columns B thru G find data about each song such as Artist, Year, Length, etc. The voice break will not use any of those items, so I have included a LEFT function in the formulas in Columns B thru G to leave those cells blank if the first character of Column A is an Exclamation Point. The problem is counting Column A to show the total count of actual songs and the total count of voice breaks. Any help is appreciated.
Thank you,
Dan Wilson...
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
For the total number of songs try
Excel Formula:
=COUNTIFS(A:A,"<>",A:A,"<>!*")
 
Solution

Dan Wilson

Active Member
Joined
Feb 5, 2006
Messages
448
Office Version
  1. 365
Platform
  1. Windows
For the total number of songs try
Excel Formula:
=COUNTIFS(A:A,"<>",A:A,"<>!*")
Good day Fluff. Good to hear from you again. I thought at first I was going to have to ask for more help. When I entered your COUNTIFS function, it showed me a number greater than than the number of song entries. Then I realized that A:A included the Totals lines below the song entries. Once I modified the A:A to A2:A30, all is well. I learned another trick with the "<>" elements. Excel is so great!
Thank you,
Dan Wilson...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,290
Members
416,962
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top