Count If across rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I read the postings on how to use a sum function to perform soemthing similar
to CountIf in excel. However, I am trying to perform a countif like function
accross rows, not down columns. To be specific, for each record, I have 5
fields, each with a change in volume for a specific month (5 Columns: July-
August). I want to take a tally- for each record, I need to know how many of
these change in volumes are positive. Essentially, i would want to create a
new colume with a count for each record, based on previous fields. Any ideas?
Thanks in advance.
Joel
 
You can add "columns" in a row by adding a calculated field or control that
will add up the other fields. To count how many of these other fields have
positive values you need to add 1 instead of the value of the field whenever
the field contains a positive number.

=Abs(Nz([Field1]>0,0) + Nz([Field2]>0,0) + Nz([Field3]>0,0) +
Nz([Field4]>0,0) + Nz([Field5]>0,0))

should give you this count. It relies on the fact that VBA/Access will treat
True as -1. So, if the value in the field is greater than zero, -1 will be
added. Use Abs() to get the positive value. The Nz() is to add zero if the
value is Null; otherwise, if any of the values is Null, the result will be
Null.
 
Should not that be <0 ?

Wayne Morgan said:
You can add "columns" in a row by adding a calculated field or control that
will add up the other fields. To count how many of these other fields have
positive values you need to add 1 instead of the value of the field whenever
the field contains a positive number.

=Abs(Nz([Field1]>0,0) + Nz([Field2]>0,0) + Nz([Field3]>0,0) +
Nz([Field4]>0,0) + Nz([Field5]>0,0))

should give you this count. It relies on the fact that VBA/Access will treat
True as -1. So, if the value in the field is greater than zero, -1 will be
added. Use Abs() to get the positive value. The Nz() is to add zero if the
value is Null; otherwise, if any of the values is Null, the result will be
Null.

--
Wayne Morgan
MS Access MVP


jjbf22 said:
I read the postings on how to use a sum function to perform soemthing
similar
to CountIf in excel. However, I am trying to perform a countif like
function
accross rows, not down columns. To be specific, for each record, I have 5
fields, each with a change in volume for a specific month (5 Columns:
July-
August). I want to take a tally- for each record, I need to know how many
of
these change in volumes are positive. Essentially, i would want to create
a
new colume with a count for each record, based on previous fields. Any
ideas?
Thanks in advance.
Joel
Click to expand...
Click to expand...
 
No, he wants to know how many have positive values so True needs to be
returned if the value is positive. Positive numbers are >0 (greater than
zero).

?1>0
True
 
Your expression is taking the absolute value after evaluation.
The field contains either a zero or a minus one.
In my math minus one is less than one.
How am I going wrong?
 
The fields, I called them Field1, Field2, etc, contain the "change in volume
for a specific month" mentioned by Joel. He said he wanted to get a count of
how many of these field (5 total) in each row contained a positive value for
this "change in volume". The inequality I test for is whether or not this
"change in volume" is greater than zero. If it is, then it is a positive
number and the inequality will return True. Access treats this True value
as -1 (and False as 0). I add up the -1's that are returned, which will give
a negative count. I then take the Abs() to change it to a positive count.

=Abs(Nz([Field1]>0,0) + Nz([Field2]>0,0) + Nz([Field3]>0,0) +
Nz([Field4]>0,0) + Nz([Field5]>0,0))

In the example above, if fields Field1 and Field5 have positive values and
the other three are 0, negative values, or Null, then the equation will be

=Abs(-1 + 0 + 0 + 0 + -1) for a result of 2. There were 2 fields that had
positive values in them.

Positive number > 0 = True (-1)
0 or Neg number > 0 = False (0)
Null > 0 = Null, but Nz() will change this to 0.

As a test from the Immediate window, putting in numbers or Null for the
field names:

?Abs(Nz(23>0,0) + Nz(Null>0,0) + Nz(-4>0,0) + Nz(0>0,0) + Nz(7>0,0))
2
 
You are correct. I went back and read all the thread. Somewhere along the
way I got it in my mind that the fields were checkboxes that were being
counted.

Wayne Morgan said:
The fields, I called them Field1, Field2, etc, contain the "change in volume
for a specific month" mentioned by Joel. He said he wanted to get a count of
how many of these field (5 total) in each row contained a positive value for
this "change in volume". The inequality I test for is whether or not this
"change in volume" is greater than zero. If it is, then it is a positive
number and the inequality will return True. Access treats this True value
as -1 (and False as 0). I add up the -1's that are returned, which will give
a negative count. I then take the Abs() to change it to a positive count.

=Abs(Nz([Field1]>0,0) + Nz([Field2]>0,0) + Nz([Field3]>0,0) +
Nz([Field4]>0,0) + Nz([Field5]>0,0))

In the example above, if fields Field1 and Field5 have positive values and
the other three are 0, negative values, or Null, then the equation will be

=Abs(-1 + 0 + 0 + 0 + -1) for a result of 2. There were 2 fields that had
positive values in them.

Positive number > 0 = True (-1)
0 or Neg number > 0 = False (0)
Null > 0 = Null, but Nz() will change this to 0.

As a test from the Immediate window, putting in numbers or Null for the
field names:

?Abs(Nz(23>0,0) + Nz(Null>0,0) + Nz(-4>0,0) + Nz(0>0,0) + Nz(7>0,0))
2

--
Wayne Morgan
MS Access MVP


KARL DEWEY said:
Your expression is taking the absolute value after evaluation.
The field contains either a zero or a minus one.
In my math minus one is less than one.
How am I going wrong?
Click to expand...
Click to expand...
 
I went back and read all the thread. Somewhere along the
way I got it in my mind that the fields were checkboxes that were being
counted.
Click to expand...

Been there, done that. :-)
 
Back
Top