Forum Discussion
Contar una frase en toda el libro
Hi, I am trying to count how many times the content fo a cell appears in the whole excel workbook. I have an array where column A has different phrases and in column B I need the count. Any Ideas?
3 Replies
yes but more details or a better yet a sample workbook would help.
what do you mean by "whole excel workbook" (i.e. multiple sheets)?
should the cell match the phrases or just contain the phrase?
what if the cell has the phrase more than 1 time?
if it is only on 1 or 2 sheets it may be easy to use COUNTIF
=COUNTIF(Sheet1!C1:ZZ1000,"*"&A1&"*")
alternatively you might try
=SUM(--ISNUMBER(SEARCH(A1,C1:ZZ1000)))
So the COUNTIF you can easy make dynamic array and add for multiple sheets:
=COUNTIF(Sheet1!C1:ZZ1000,"*"&A1:A100&"*") + COUNTIF(Sheet2!A1:ZZ1000,"*"&A1:A100&"*")
But if you have lots of sheets or unknown number you can get tricky with the second as long as you know the first and last sheet (you can use hidden sheets to help) and as long as the total number of sheets and areas on the sheets aren't too big
=SUM(--ISNUMBER(SEARCH(A1, VSTACK( First_Sheet:Last_Sheet!$A$1:$ZZ$1000))))
to make that a dynamic array you would just need to wrap that with MAP or BYROW helper LAMBDA function
Thanks for your comments.
To be more specific, I want to measure how many hours I am spending on each activity as the year goes on. I have a sheet for every week. If a new activity appears, I'll add it to the table on the right before using it on the table to the left so that the match will be exact. Real table measure activities each "half hour", 8 hour a day.
Week 1 day 1 day 2 day 3 Activity hours spent hour 1 team meeting seminar O&P Meeting team meeting 1 hour 2 project 1 seminar project 2 project 1 4 hour 3 project 1 project 1 project 2 project 2 3 hour 4 project 1 report project 2 report 3 hour 5 report vendor visit report vendor visit 1 O&P Meeting 1 Try the attached:
