Excel formula help!

[NurseTracie]

Hi all!

I am trying to calculate the number of times a phrase occurs in a specific cell range on multiple Excel sheets.  I need to count the numbers occurrences from 202 different sheets with the same cell range on each sheet.  Each sheet is the name of an individual in the format Lastname, Firstname.  I have tried multiple different formulas and cannot find one that works.

This is what I am trying currently -

=SUM(COUNTIF(INDIRECT("'"&{"Last, First:Lastname, Firstname"}&"'!i14:v35"),"Blank required field"))

and I am getting a #REF! error.

I have also referenced a cell# with the phrase I am looking for and I get the same error - 

=SUM(COUNTIF(INDIRECT("'"&{"Last, First:Lastname, Firstname"}&"'!i14:v35"),A2))

I have also tried -

=SUM('Last, First:Lastname, Firstname'!I14:V35,"Blank required field")

and I get a #VALUE! error.

I have also referenced a cell# with the phrase I am looking for and I get a result of 0 even though there are at least 3 occurrences of the phrase - 

=SUM('Last, First:Lastname, Firstname'!I14:V35,A2)

The formula above works fine if I am only looking at data from 1 sheet, but I need to be looking at data from a range across 202 sheets.

I would like the formula to count how many times the phrase "Blank required field" is mentioned for the month of January (cell range I14:V35) on the 202 different sheets and to return a numeric value/count of the occurrences.  Keep in mind, some of the cells in this range on each of the sheets are merged cells.  Does that matter?  Is what I am trying to do even possible?  I have watched a ton of videos and read a ton of resources but this seems way over my head.  :)

Any help is much appreciated!

Thank you!

NurseTracie

[#2 · Accepted solution]

Hi Tracie, I see you tagged '365' so you have access to VSTACK. This will make things easier and INDIRECT is not needed.

First, use VSTACK to combine all the sheets by supplying a 3D reference.  Next, determine if the 'input' (the cell containg the text) is equal to the 'stack' (the combined ranges).  The result will be a matrix of TRUE and FALSE.  N converts those to 1s and 0s then SUM adds them up.

=LET(
    stack, VSTACK(Sheet1:Sheet3!$A$1:$J$30),
    SUM(N(input = stack))
)

[#3]

Thanks Patrick!

So do I use the formula you provided above?  Or do I need to change stuff in it beside the sheet names?  Are Input and Stack supposed to be changed to something?  How does this formula looks for the phrase I am searching for?  I think I am really out of my league here.

I tried -

=LET(STACK,VSTACK(Last, First:Lastname, Firstname!$I$14:$V$35),SUM(N(INPUT=STACK)))

and I get a #NAME? error.

[#4]

I figured it out! Thank you Patrick!

[#5]

Glad it worked. Have a great day!