# CountIF + sumproduct

#### Dark0Prince

##### Active Member
=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?

Last edited:

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

#### Eric W

##### MrExcel MVP
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

##### MrExcel MVP
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

##### Active Member
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

##### Board Regular
=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

Last edited:

Replies
0
Views
249
Replies
1
Views
188
Replies
2
Views
201
Replies
3
Views
221
Replies
6
Views
1K

1,190,836
Messages
5,983,174
Members
439,825
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back