Counting non blanks in a table from multiple columns

[tsadams23]

Hello everyone, it's been a while since I've posted here and I apologize if this has already been asked, I didn't seem to find what I was looking for.

I'm trying to count non-blank cells from multiple columns in a table but i only want it to count specific columns. I'll do my best to illustrate.

Employee	Department	Title	Priority	Title:2	Priority:2	Title:3	Priority:3
John Doe	HR	Fix something	Fixing Something will help everyone	Don't Do Something	I would like it if you didnt do something
Jane Doe	Finance	Take a shower	Taking a shower will make people like you	Call people by their names	calling people by their names will make them like you	Don't bite your nails	It's a bad habit and no one likes it
Bob Dobbs	IT

I would like to add a column on the left side of this table which will count the number of "titles" essentially giving me a number of priorities that are entered for each employee. Given the table above i would hope to see.

2 in the column next to John Doe's name
3 for Jane Doe
0 for Bob Dobbs

I get table when i dump the results of our performance management software and it retains the column name for Title and Priority which is why they start being numbers sequentially, and there are a few people who have 5 or 6 priorities listed so there's a lot of title:X columns.

I did do a formula =sumproduct(counta(title,title:1,title:2,etc.) which does work, but I know there has to be a better more efficient way. I'm going to have to redo this several times and dont want to have to select a bunch of cells each time. The report from the performance management software dumps into a tab delimited text file so i have to copy and paste the data into excel each time i want to refresh this report for managment.

[Pete_UK]

Assuming that table starts in cell A1, you could do this in, say, Z2:

=SUMPRODUCT((ISNUMBER(SEARCH("Title",$C$1:$Y$1))*(C2:Y2<>""))

Then copy down.

Hope this helps.

Pete

[tsadams23]

That worked perfectly, thank you so much!!!

[Pete_UK]

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete