Use a function on a indirect range

Try this...

=SUMIF(INDIRECT("'" & ABS($A4) & "'!C:C"); "D"; INDIRECT("'" & ABS($A4) & "'!D:D"))

Is this somehow related to PHP?

No, not PHP
Wayne, not ABS($A4)
The referenced range contains positive and negative value (in the other sheet)
I want the sum of ABS values
The ABS must be applied to the values, not the references

Try

=SUMPRODUCT((INDIRECT("'"&$A4&"'!C:C")="D")*ABS(INDIRECT("'"&$A4&"'!D:D")))

You can't apply functions in the sum range with SUMIF. You could either use 2 SUMIFS and subtract the negative result:

=SUMIFS(INDIRECT("'"&$A4&"'!D:D"),INDIRECT("'"&$A4&"'!C:C");"D";INDIRECT("'"&$A4&"'!D:D"),">0")-=SUMIFS(INDIRECT("'"&$A4&"'!D:D"),INDIRECT("'"&$A4&"'!C:C");"D";INDIRECT("'"&$A4&"'!D:D"),"<0")

or use a SUMPRODUCT:
=SUMPRODUCT((INDIRECT("'"&$A4&"'!C:C")="D")*(ABS(INDIRECT("'"&$A4&"'!D:D"))))

Well, now I get #VALUE! as result

With which formula? If it's the SUMPRODUCT one, you probably have text values in at least one of the data rows. Change the ranges to not include header rows and any other text rows, if you can.

Can you provide a small Excel example ?
I have the french version ... will be easier (automatic translation of formula)
My current formula (without ABS) and working fine (some non numeric cells present) was :
=SOMME.SI(INDIRECT("'"&$A4&"'!C:C");"D";INDIRECT("'"&$A4&"'!D:D"))

Exactly what I need !
Many thanks