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

 

[just_jon]

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

http://www.mrexcel.com/board2/viewtopic.php?p=870186&highlight=#870186

 

[wesimmo]

Apologies?

Does the little devil thing mean i did something wrong?

 

[just_jon]

Re: Apologies?

Does the little devil thing mean i did something wrong?

Duplicate post.

Always best to stay to one, or at least provide a link to any others.

 

[PM1]

Can I have an expansion on the SUMPRODUCT if at all possible.The formula

=SUMPRODUCT(('2305 Assets'!$N$2:$N$3416>0)*('2305 Assets'!$N$2:$N$3416<=25000)) produced an answer of 528.

Now within that 528 there is another column which records a tag beginning LN followed by 6 digits. I now need to sum how many actually have the tag, and then in the next column to this answer report on what % this is of the 528. That part I think is easy (I hope)

 

[Andrew Poulsom]

Can I have an expansion on the SUMPRODUCT if at all possible.The formula

=SUMPRODUCT(('2305 Assets'!$N$2:$N$3416>0)*('2305 Assets'!$N$2:$N$3416<=25000)) produced an answer of 528.

Now within that 528 there is another column which records a tag beginning LN followed by 6 digits. I now need to sum how many actually have the tag, and then in the next column to this answer report on what % this is of the 528. That part I think is easy (I hope)

The column with the tag being which column? Let's assume it's P:

=SUMPRODUCT(('2305 Assets'!$N$2:$N$3416>0)*('2305 Assets'!$N$2:$N$3416<=25000))*(LEFT('2305 Assets'!$P$2:$P$3416,2)="LN"))

 

[PM1]

Hi Andrew,

This produced a result of 0, I tied changing the ,> values but still 0. I also noticed an extra ")" at the end was this a typo?

 

[Andrew Poulsom]

Yes, that's a typo, but in the middle not at the end:

=SUMPRODUCT(('2305 Assets'!$N$2:$N$3416>0)*('2305 Assets'!$N$2:$N$3416<=25000)*(LEFT('2305 Assets'!$P$2:$P$3416,2)="LN"))

 

[PM1]

So frustrating, now produces the dreaded #N/A

 

[Brian from Maui]

So frustrating, now produces the dreaded #N/A

Do you have any #N/A in your ranges?

 

[PM1]

Do you have any #N/A in your ranges?

Nope - Only items beginning with LN and blanks