CountIF + sumproduct

[Dark0Prince]

=SUMPRODUCT(--(JOET!$H$1:$H$4999>=42339),--(JOET!$H$1:$H$4999<=42369),--(JOET!$A$1:$A$4999=8568),IF(1/COUNTIF(JOET!G:G,"<=1000")=1,1,0)))

So I'm attempting to COUNTIFS a sum range if other statements are true. The problem I think is the end of my statement COUNTIF(JOET!G:G,"<=1000")=1,1,0)))

I only want to count if there is an amount <=1000. Did I write that wrong?

 

[Eric W]

If I understand correctly, maybe:

=SUMPRODUCT(--(JOET!$H$1:$H$4999>=42339),--(JOET!$H$1:$H$4999<=42369),--(JOET!$A$1:$A$4999=8568))*(COUNTIF(JOET!G:G,"<=1000")>0)

 

[Tetra201]

Maybe just:

=SUMPRODUCT(--(JOET!$H$1:$H$4999>=42339),--(JOET!$H$1:$H$4999<=42369),--(JOET!$A$1:$A$4999=8568),--(JOET!$G$1:$G$4999<=1000))

 

[Dark0Prince]

Maybe just:

=SUMPRODUCT(--(JOET!$H$1:$H$4999>=42339),--(JOET!$H$1:$H$4999<=42369),--(JOET!$A$1:$A$4999=8568),--(JOET!$G$1:$G$4999<=1000))

That worked thanks

 

[WarfritLive]

=SUMPRODUCT(--(JOET!$H$1:$H$4999>=42339),--(JOET!$H$1:$H$4999<=42369),--(JOET!$A$1:$A$4999=8568),IF(1/COUNTIF(JOET!G:G,"<=1000")=1,1,0)))

So I'm attempting to COUNTIFS a sum range if other statements are true. The problem I think is the end of my statement COUNTIF(JOET!G:G,"<=1000")=1,1,0)))

I only want to count if there is an amount <=1000. Did I write that wrong?

The reason your original formula does work is the arrays are not all the same size. That G:G should match the others. Ie G1:G4999

Edit: FYI the formula you settled on -

SUMPRODUCT(--(JOET!$H$1:$H$4999>=42339),--(JOET!$H$1:$H$4999<=42369),--(JOET!$A$1:$A$4999=8568),--(JOET!$G$1:$G$4999<=1000))

Is NOT the same as the original. The original only counts column G if it's unique within that column and,under 1000. The one you settled on counts everything in column G under 1000 with no consideration for duplicates