COUNTIF ?

PM1

Board Regular
Joined
Oct 28, 2005
Messages
192
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Andrew,

As ever you were right :oops: - I did have the range wrong.

Anyway it now works, Thankyou.

Next ?, what do I use if the I use another column which just has data that is not in any particular formatand I want to count where both columns has data in the said range.

I was trying :

=SUMPRODUCT(('2305 Assets'!$N$2:$N$3416=0)*('2305 Assets'!$N$2:$N$3416=0)*(SUM(LEFT('2305 Assets'!$A$2:$A$3416,2)="LN")-('2305 Assets'!$B$2:$B$3416>0)))

but that just gave me a Neg value
 
Upvote 0
If you are only comparing two columns (as your post seems to indicate) and you are only trying to count columns with values (you didn't specify text or numeric) you could simplify to:

Both columns are text:
Code:
=sumproduct(([range1]>"")*([range2]>""))

One column is text the other numeric:
Code:
=sumproduct(([range1]>"")*([range2]>0))
 
Upvote 0
PM1 said:
Andrew,

As ever you were right :oops: - I did have the range wrong.

Anyway it now works, Thankyou.

Next ?, what do I use if the I use another column which just has data that is not in any particular formatand I want to count where both columns has data in the said range.

I was trying :

=SUMPRODUCT(('2305 Assets'!$N$2:$N$3416=0)*('2305 Assets'!$N$2:$N$3416=0)*(SUM(LEFT('2305 Assets'!$A$2:$A$3416,2)="LN")-('2305 Assets'!$B$2:$B$3416>0)))

but that just gave me a Neg value

You're mixing coercers, either all * or --. In the above case, you have only - instead of --, thus the neg value. Are you summing?
 
Upvote 0
Brian - (lucky sod from Maui) :wink:

I have this formula:-

=SUMPRODUCT(('2305 Assets'!$N$2:$N$3416=0)*('2305 Assets'!$N$2:$N$3416=0)*(SUM(LEFT('2305 Assets'!$A$2:$A$3416,2)="LN"))

which works and tells me how many items I have in range = 0 and how many of them have a "LN" No recorded (Col A). Now in Col "B" I have the Serial No recorded and I have copied the exact formula as above but used "$B" instead of "$A".

So I have 2 results which at the moment is fine, however I know need to calculate how many records in the range have either a LN No or a Ser No because there are occasions where both are present and/or one is present.

:unsure:

So in layman's terms in the range N2:N3416, if items were =0, I could have 1500 items with LN No's and 3000 items with a Ser No, however the actual total could be 3300.
 
Upvote 0
Could I use a range spanning 2 columns to report this?

=SUMPRODUCT(('2305 Assets'!$N$2:$N$3416=0)*('2305 Assets'!$N$2:$N$3416=0)*('2305 Assets'!$A$2:$b$3416)>0))
 
Upvote 0
Ok, that didn't work, can I use the OR function within SUMPRODUCT, so I would have:

=SUMPRODUCT(('2305 Later 1st Jul'!$N$2:$N$37>0)*('2305 Later 1st Jul'!$N$2:$N$37<=25000)*(LEFT('2305 Later 1st Jul'!$A$2:$A$37,2)="LN")OR(*('2305 Later 1st Jul'!$B$2:$B$37>0)
)
 
Upvote 0
Try:

=SUMPRODUCT(--('2305 Later 1st Jul'!$N$2:$N$37>0),--('2305 Later 1st Jul'!$N$2:$N$37<=25000),--(LEFT('2305 Later 1st Jul'!$A$2:$A$37,2)="LN"),--('2305 Later 1st Jul'!$B$2:$B$37>0))
 
Upvote 0
Hi Andrew,

That produced zero.

I know that out of 36 items in Col A there are 16 which have a LN No, and in Col B there are 12 items with a Ser No. Out of the 36 items I can see that there are 19 items that either have one or the other or both.

My trouble is that i have 5 other sheets where I get into 000's of lines hence I cannot do it manually.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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?

Disable AdBlock

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
Back
Top