Need to Count Text Entries Across Multiple Sheet That Correspond With Criteria in the Same Row

[grnaskd]

Hello,

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.

 

[Aladin Akyurek]

If I3:I338 is not either solely text-based or solely numeric, the calculations will go wrong:

Solely text-based:

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

Solely numeric:

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

If both, i.e., mixed:

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

 

[grnaskd]

Is there a way to count empty spaces for specific criteria? For example how many beds are not occupied under certain units?

 

[Aladin Akyurek]

Is there a way to count empty spaces for specific criteria? For example how many beds are not occupied under certain units?

You mean counting records with I3:I338 empty or blank?

 

[grnaskd]

Counting the records in G3:G338 that are empty or blank. This is the column that ranks are imputed and if I can count how many bed spaces aren't being occupied, I can use:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$I$2:$I$50&"'!I3:I338"),"*"&A22&"*"))

To subtract how many bed spaces are allotted for a unit from the bed spaces they aren't using to get how many are in use.

 

[Aladin Akyurek]

Counting the records in G3:G338 that are empty or blank. This is the column that ranks are imputed and if I can count how many bed spaces aren't being occupied, I can use:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&$I$2:$I$50&"'!I3:I338"),"*"&A22&"*"))

To subtract how many bed spaces are allotted for a unit from the bed spaces they aren't using to get how many are in use.

I don't see beds... We must talk in terms of ranges and cells which house the criteria the ranges must meet.

$I$2:$I$50 houses sheet names.

Now spell the ranges one by one along with the criterion each must meet.