Formula to count text in every nth column in another worksheet while looking up a specific column

[Jermine]

Hi, I wish to count every 5th column starting at a specific cell, take C28 as an example in this case, meanwhile looking up to match the CSE Name in column B.
and I wish to avoid using COUNTIF function cause the value will shows error when the reference workbook is closed.

Is there a reasonably short formula for this please?

In a nutshell, I want to know the total count of "W" for each "CSE Name" (in column B) within the range A27:V47.
Meanwhile I have 2 worksheet that I need to perform such with the "CSE Name" displaying in different sequence, and needing to sum up the total count of "W" for the same person.

 

[Fluff]

How about like

=SUMPRODUCT((Sheet1!B28:B47=A2)*(Sheet1!G27:V27="Actual")*(Sheet1!G28:V47="W"))

 

[Jermine]

How about like

=SUMPRODUCT((Sheet1!B28:B47=A2)*(Sheet1!G27:V27="Actual")*(Sheet1!G28:V47="W"))

Hi Fluff, this work wonder.

May I also know how to apply the above formula in a diff. workbook with the "CSE Name" displaying different sequence (eg: workbook (a)
That is to say that, I would like to apply the above formula in workbook (a) with workbook (b) as my external reference, while the lookup value, 'CSE Name' in both workbook (a)&(b) is not in sequence.

Appreciate if you could guide me through this.

Example:
workbook (a)

workbook (b) - same image in thread #1

 

[Fluff]

If you open both workbooks & manually change the ranges in the formula by selecting the relevant cells, you will get the right construct.

 

[Jermine]

If you open both workbooks & manually change the ranges in the formula by selecting the relevant cells, you will get the right construct.

Thanks Fluff, that solved it! Thanks a million

 

[Fluff]

You're welcome & thanks for the feedback.