Sumifs excluding a range of reference values

[cgsierra]

Hello,
I'm trying to write a formula that excludes a set of values
in sheet 1, in cells A1 through A5 I have Name 1, Name 2, Name 3, Name 4, Name 5.
In sheet 2, in pivot table format I have hundreds of names in column A, in column B I have the corresponding revenue for each name, and in column C I have the Gross Profit which can be positive or negative.
I want to retrieve the total revenue from column B in sheet 2 excluding the names in sheet 1, excluding the names with negative Gross Profit and excluding the Grand Total line from the pivot table.
I have something like this:
=SUMIFS(B:B,A:A,"<>'Sheet 1'!A1:A5",A:A,"<>Grand Total",C:C,">0")
It doesnt seem to be working. It does not exclude the revenue related to the names in 'Sheet 1'!A1:A5
can you please help me?

 

[cgsierra]

I also tried the following formula but it doesn't exclude the revenue corresponding to the names in 'Sheet 1'!A1:A5 either:
=(SUMPRODUCT(SUMIFS(B:B,A:A,"<>'Sheet 1'!A1:A5",A:A,"<>Grand Total",C:C,">=0")))

Hello,
I'm trying to write a formula that excludes a set of values
in sheet 1, in cells A1 through A5 I have Name 1, Name 2, Name 3, Name 4, Name 5.
In sheet 2, in pivot table format I have hundreds of names in column A, in column B I have the corresponding revenue for each name, and in column C I have the Gross Profit which can be positive or negative.
I want to retrieve the total revenue from column B in sheet 2 excluding the names in sheet 1, excluding the names with negative Gross Profit and excluding the Grand Total line from the pivot table.
I have something like this:
=SUMIFS(B:B,A:A,"<>'Sheet 1'!A1:A5",A:A,"<>Grand Total",C:C,">0")
It doesnt seem to be working. It does not exclude the revenue related to the names in 'Sheet 1'!A1:A5
can you please help me?