Countif/Sumproduct - Multiple Worksheets/3 Criteria

[mdotbdot]

Hello

I am trying to get a count of rows which meet 3 criteria, across multiple tabs.

On a single worksheet, the formula (x)

=SUMPRODUCT((B:B<=4)*(B:B>=1)*(A:A="m"))

gives the expected result "4". However, when this table appears on multiple sheets, I tried

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'!B:B"),"<=4")*(COUNTIF(INDIRECT("'"&Sheetlist&"'!B:B"),">=1")*(COUNTIF(INDIRECT("'"&Sheetlist&"'!A:A"),"M"))))

where Sheetlist! is a named range listing all tabs. The resulting calculation is much larger than expected - it appears to calculate each tab multiple times. The Formula (z)

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheetlist&"'!A:A"),"M"))

gives expected results for a single criterion, but for three something goes wrong. My logic was to replace the expressions in formula (x) with variations on (z) for each criteria to get but that is not the solution. Any advice on this one? The workbook has 272 tabs. Simplified data table is below.

FYI I modified these formulas from threads on Mr. Excel msg board, so thanks for getting me this far.

P.S. This is my first post.

[TABLE="class: grid, width: 50"]
<TBODY>[TR]
[TD]Gender
[/TD]
[TD]Years Of Service
[/TD]
[/TR]
[TR]
[TD]m
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]m
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]m
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]m
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]f
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]f
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]f
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]f
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]m
[/TD]
[TD]2
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[mdotbdot]

P.S. I'm using Excel 2010.

 

[Aladin Akyurek]

P.S. I'm using Excel 2010.

Although a late response, the following would suit your set up:

=SUMPRODUCT(
    COUNTIFS(
      INDIRECT("'"&SheetList&"'!B:B"),">=1",
      INDIRECT("'"&SheetList&"'!B:B"),"<=4",
      INDIRECT("'"&SheetList&"'!A:A"),"M"))