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

grnaskd

New Member
Joined
Dec 2, 2014
Messages
9
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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

New Member
Joined
Dec 2, 2014
Messages
9
Is there a way to count empty spaces for specific criteria? For example how many beds are not occupied under certain units?
 

grnaskd

New Member
Joined
Dec 2, 2014
Messages
9
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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.
 

Forum statistics

Threads
1,085,033
Messages
5,381,309
Members
401,733
Latest member
Kabasa007

Some videos you may like

This Week's Hot Topics

Top