COUNTIFS with SUMPRODUCT

[mrbeanyuk]

Good evening

I am trying to apply a COUNTIFS statement so that I can search through a range of sheets to confirm that criteria is met in column B and column Q then count. I have written the following and it returns 0 even though I know there are serials that match the criteria. Have I missed something in the following formula? Thanks very much!

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$B$51:$B$67&"'!$B:$B"),D$44,(INDIRECT("'"&$B$51:$B$67&"'!$Q:$Q")),{"Future on Track";"Completed"}))

 

[RasGhul]

Seems to be missing your Sheet references;

[COLOR=#333333]=SUMPRODUCT(COUNTIFS(INDIRECT([/COLOR]"'Sheet2:Sheet6'!$B$51:$B$67,$B:$B"[COLOR=#333333]),D$44,(INDIRECT([/COLOR]"'Sheet2:Sheet6'!$B$51:$B$67,$Q:$Q"[COLOR=#333333])),{"Future on Track";"Completed"}))[/COLOR]

 

[Aladin Akyurek]

@ mrbeanyuk

1. Does D44 occur in column B of the relevant sheets? If not, a 0 result will obtain.

2. Does any of the pair "Future on Track" and "Completed" occur in column Q of the relevant sheets? If not, a 0 result will obtain.


Are either Future on Track or Completed stand-alone occurrences in column Q? If not, you might want to try:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$B$51:$B$67&"'!B:B"),D$44,(INDIRECT("'"&$B$51:$B$67&"'!Q:Q")),{"*Future on Track*";"*Completed*"}))

where B51:B67 is assumed to list the relevant sheets.