SUMPRODUCT + Name Manager

[lukasz_rz]

Hi Guys.

I need to count rows where:
- There is a status Opened in column D (Opened is a range in Name Manager, so there is more than one possible status)
- There is a digit 1 in column I
- There is status PG in column B (PG is a range in Name Manager, so there is more than one possible status)

=SUMPRODUCT(COUNTIFS(INDIRECT($B$13&"!D:D");Opened;INDIRECT($B$13&"!I:I");1;INDIRECT($B$13&"!B:B");PG))

where INDIRECT($B$13&"!D:D") simply means Worksheet!D:D.

Whatever I do, I get 0, even though there are rows that meet all the requirements. What did I do wrong?

 

[Aladin Akyurek]

Does this work for you?

=SUMPRODUCT(--ISNUMBER(MATCH(INDIRECT("'"&$B$13&"'!D2:D400");Opened;0));--(INDIRECT("'"&$B$13&"'!I2:I400")=1);--ISNUMBER(MATCH(INDIRECT("'"&$B$13&"'!B2:B400");PG;0)))

 

[lukasz_rz]

Does this work for you?

=SUMPRODUCT(--ISNUMBER(MATCH(INDIRECT("'"&$B$13&"'!D2:D400");Opened;0));--(INDIRECT("'"&$B$13&"'!I2:I400")=1);--ISNUMBER(MATCH(INDIRECT("'"&$B$13&"'!B2:B400");PG;0)))

Wow, as far as I checked, it works! Thanks a lot, you are a true genius

 

[Aladin Akyurek]

Wow, as far as I checked, it works! Thanks a lot, you are a true genius

Glad to see it worked. Thanks for the kind update.