# COUNTIF ?

#### PM1

##### Board Regular
I have a Sheet with 3415 lines each with a value. Overall they total £7.5m.

I'm trying to create a formula that counts the no of items >0 .AND. <25000.

I looked at doing it in a PIvot Table but could not work out how to change the result, ie 3415 should only be 200 items > 0 but <25000

Can I use Countif as well as ()AND, if so how?

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### Joe4

##### MrExcel MVP, Junior Admin
Use the SUMPRODUCT formula. Assuming all of your values are in column A (rows 1-3415), it would look something like this:

=SUMPRODUCT((A1:A3415>0)*(A1:A3415<25000))

#### Andrew Poulsom

##### MrExcel MVP
Use SUMPRODUCT like this:

=SUMPRODUCT(--(A1:A100>0),--(A1:A100<25000))

counting:

=sumproduct(--(
#VALUE!

#### just_jon

##### Legend
PM1 said:
I have a Sheet with 3415 lines each with a value. Overall they total £7.5m.

I'm trying to create a formula that counts the no of items >0 .AND. <25000.

I looked at doing it in a PIvot Table but could not work out how to change the result, ie 3415 should only be 200 items > 0 but <25000

Can I use Countif as well as ()AND, if so how?

=COUNTIF(Range,">0")-COUNTIF(Range,">=25000")

Note this will exclude items of exactly 25k.

#### PM1

##### Board Regular
OK thats works I think but just to expand on it a little. I am trying to create a matrix based on my sheet of 3415 lines which shows:-
Master Asset File.xls
BCDEF
2TotalAssets
3NoValue
4InitialAssetRegister(2305)34157,020,066
5InitialAssetRegister(2305)Software330
6InitialAssetRegister(2305)PhysicalAssets33827,020,066
7AssetsNBV>0<25k3353
8AssetsNBV>25k<50k
9AssetsNBV>50k<100k
10AssetsNBV>100k
Sheet1

As you can see I need to count the no of items in a range then sum those items. While I think I can count them, how do I sum them?

#### Joe4

##### MrExcel MVP, Junior Admin
If you simply want to SUM the fields you are counting, then expanding on the original formula I gave you:

=SUMPRODUCT((A1:A3415>0)*(A1:A3415<25000)*(A1:A3415))

#### wesimmo

##### Board Regular
seeing as you appear to be experts on SUMPRODUCT :wink:

Can you sumproduct the values of cells in two ranges depending on the appropriate cell in a third range meeting a condition?

#### Joe4

##### MrExcel MVP, Junior Admin
Sure. The only requirement with SUMPRODUCT is that the ranges need to be the same size, i.e. all have the same number of rows.

#### wesimmo

##### Board Regular
=SUMPRODUCT(--('Volumetrics - Collections'!E\$1:\$N\$1=\$F\$1),'Volumetrics - Collections'!\$E\$15:\$N\$15,'Volumetrics - Collections'!\$E\$18:\$N\$18)

Thanks to erik.van.geit for the formulae above.

Basically sumproducts the ranges on rows 15 and 18 when the range on row 1 = cell f1.

Replies
11
Views
965
Replies
6
Views
131
Replies
3
Views
169
Replies
4
Views
311
Replies
6
Views
145

1,195,670
Messages
6,011,074
Members
441,581
Latest member
rp4717

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