This is Military Spreadsheet that involves troop movement so I have to be very vague but I'll try not to be confusing.

Here's a problem I'm having. I need to count how many times text (any text/numbers/combination of the two) appear in a column across multiple sheets.

Here's the details of the sheets. Think of it as a hotel with multiple buildings with each building being on a separate sheet. Each Room has three beds which will be on three different rows. There are 76 rooms total so 228 rows of beds/rooms.

In Column (I) I have the Units

In Column (G) I have the Ranks (E1,O1,W1,CIV, etc)

I need to count how many times an entry is made in Column G, but only the entries for the Units (Column I) that I specify.

Here's what I have but it's returning only zero:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&H2:H50&"'!G3:G338"),INDIRECT("'"&H2:H50&"'!I3:I338"),"*"&A21&"*"))

Let me know if any more information or clarification is needed, I hope someone can please help.