COUNTIF ?

[PM1]

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?

Thanks in advance

 

[Joe4]

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]

Use SUMPRODUCT like this:

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

 

[nbrcrunch]

counting:

=sumproduct(--(

#VALUE!

 

[just_jon]

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?

Thanks in advance

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

Note this will exclude items of exactly 25k.

 

[PM1]

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
	B	C	D	E	F
2				TotalAssets
3				No	Value
4	InitialAssetRegister(2305)			3415	7,020,066
5	InitialAssetRegister(2305)Software			33	0
6	InitialAssetRegister(2305)PhysicalAssets			3382	7,020,066
7	AssetsNBV>0<25k			3353
8	AssetsNBV>25k<50k
9	AssetsNBV>50k<100k
10	AssetsNBV>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]

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]

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]

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]

=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.