Not to inlcude zero in COUNT

[enna49]

Hi
Please can you help with this problem.
I need a count in a column with amounts. The problem being I do require to
count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria
that this applies to. eg
If Cols B, C and D meet the criteria count any activity in N.
Thanking you
Anne

 

[Jacob Skaria]

Try

=SUMPRODUCT((B1:B100=criteria1)*(C1:C100=criteria2)*
(D1100=criteria3)*(ISNUMBER(N1:N100))*(N1:N100<>0))

With criterias in J1, J2, J3 try the below which will count the number of
values in N1:N100 except zeroes and blanks

=SUMPRODUCT((B1:B100=J1)*(C1:C100=J2)*(D1100=J3)*
(ISNUMBER(N1:N100))*(N1:N100<>0))

If this post helps click Yes

 

[T. Valko]

Need more specific details.

If Cols B, C and D meet the criteria

What are the criteria?

I do require to count the Credits and Debits,
but NOT the 0 (ZEROS).

So, are credits positive numbers or do you mean the word credit? Are debits
negative numbers or do you mean the word debit? If credits and debits are
numbers and you want to exclude 0s is there any text in that range?

 

[Ms-Exl-Learner]

Use the Count or Counta function and use countif function to substract the 0
(Zero) values.

Counta function will Count Text, Number & Spaces.
=COUNTA(B)-COUNTIF(B,0)

Count function will count only the Numeric values.
=COUNT(B)-COUNTIF(B,0)

Remember to Click Yes, if this post helps!

 

[enna49]

Hi

If Col B = QN
If Col C = IM
If Col D = R
count any Outstandings in Col N. (These are amounts and can be a Debit or
Credit. I do not want them to SUM, just COUNT the no of entries and NOT
include any Zeros.)

 

[T. Valko]

Try this...

If column N contains numbers only (or possibly empty cells)

=SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D210="R"),--(N2:N10<>0))

Better to use cells to hold the criteria:

P1 = QN
Q1 = IM
R1 = R

=SUMPRODUCT(--(B2:B10=P1),--(C2:C10=Q1),--(D210=R1),--(N2:N10<>0))

 

[enna49]

Hi
Following on from this and thank you for your help, is there a way that this
will select in col D all characters except 1 or 2 (eg I would like to
accept everthing except A)

Thanks

Anne

 

[T. Valko]

I would like to accept everthing except A

Try something like this:

=SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D210<>"A"),--(N2:N10<>0))

Note that empty cells in column D will meet that condition. If you might
need to account for that then use:

=SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D210<>""),--(D210<>"A"),--(N2:N10<>0))

 

[enna49]

You little ripper. Thank you

Try something like this:

=SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D210<>"A"),--(N2:N10<>0))

Note that empty cells in column D will meet that condition. If you might
need to account for that then use:

=SUMPRODUCT(--(B2:B10="QN"),--(C2:C10="IM"),--(D210<>""),--(D210<>"A"),--(N2:N10<>0))

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

You little ripper. Thank you