SUM BY CRITERIA

[zyus]

I hv this records of data :

Arr Amt
---- -----
0 100
2 100
2 200
3 100

When i use this =Sum(Abs([Arr]=2)) i get 2 as result..

Q...How to sum the Amt where the criteria is same as above ctrl..
i expect the sum to be 300

Thanks

 

[Van T. Dinh]

Try:

=DSum("Amt", "YourTable", "[Arr] = 2")

 

[zyus]

Thanks.

Just want to ask you how to use =Sum(Abs([Arr]=2)) if i want to range the
Arr say between 2 to 5, i tried >1<6 but it wont work...

Try:

=DSum("Amt", "YourTable", "[Arr] = 2")

--
HTH
Van T. Dinh
MVP (Access)

I hv this records of data :

Arr Amt
---- -----
0 100
2 100
2 200
3 100

When i use this =Sum(Abs([Arr]=2)) i get 2 as result..

Q...How to sum the Amt where the criteria is same as above ctrl..
i expect the sum to be 300

Thanks

 

[Van T. Dinh]

Beware that the expression

=Sum(Abs([Arr]=2))

does not calculate as you expected. It is calculate this way:

* [Arr] = 1 is evaluated as a Bolean expression returning False (0) and True
(-1)
In your case, you have 2 lots of -1 and 2 lots of 0.
* Abs function then take the magnitude of the values so you have 2 lots of 1
and 2 lots of 0.
* Sum then adds them up so the final result is 2.

I don't think you expected the calculations as above?

To get between 2 and 5, you can use something like:

=DSum("Amt", "YourTable", "([Arr] > 2) And ([Arr] < 6)")


--
HTH
Van T. Dinh
MVP (Access)

Thanks.

Just want to ask you how to use =Sum(Abs([Arr]=2)) if i want to range the
Arr say between 2 to 5, i tried >1<6 but it wont work...

Try:

=DSum("Amt", "YourTable", "[Arr] = 2")

--
HTH
Van T. Dinh
MVP (Access)

I hv this records of data :

Arr Amt
---- -----
0 100
2 100
2 200
3 100

When i use this =Sum(Abs([Arr]=2)) i get 2 as result..

Q...How to sum the Amt where the criteria is same as above ctrl..
i expect the sum to be 300

Thanks

 

[zyus]

What if i want to add additional criteria...say [arr] =2 and [colour]=red...

How to add it in your formula below....Thanks again

Try:

=DSum("Amt", "YourTable", "[Arr] = 2")

--
HTH
Van T. Dinh
MVP (Access)

I hv this records of data :

Arr Amt
---- -----
0 100
2 100
2 200
3 100

When i use this =Sum(Abs([Arr]=2)) i get 2 as result..

Q...How to sum the Amt where the criteria is same as above ctrl..
i expect the sum to be 300

Thanks

 

[Douglas J. Steele]

=DSum("Amt", "YourTable", "[Arr] = 2 And [colour]='red'")

Note the single quotes around red. You could also use double quotes, but to
insert a double quote inside of double quotes require that you double up the
quotes:

=DSum("Amt", "YourTable", "[Arr] = 2 And [colour]=""red""")

Note, too, that should your field or table name include blanks, you need
square brackets around the name:

=DSum("Amt", "[Your Table]", "[Arr] = 2 And [colour]='red'")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What if i want to add additional criteria...say [arr] =2 and
[colour]=red...

How to add it in your formula below....Thanks again

Try:

=DSum("Amt", "YourTable", "[Arr] = 2")

--
HTH
Van T. Dinh
MVP (Access)

I hv this records of data :

Arr Amt
---- -----
0 100
2 100
2 200
3 100

When i use this =Sum(Abs([Arr]=2)) i get 2 as result..

Q...How to sum the Amt where the criteria is same as above ctrl..
i expect the sum to be 300

Thanks

 

[zyus]

What if i want to make some exception...

For example [Arr]=2 and colour='red' and [day]=other than 7 (meaning number
other than 7 is included)

Thanks

=DSum("Amt", "YourTable", "[Arr] = 2 And [colour]='red'")

Note the single quotes around red. You could also use double quotes, but to
insert a double quote inside of double quotes require that you double up the
quotes:

=DSum("Amt", "YourTable", "[Arr] = 2 And [colour]=""red""")

Note, too, that should your field or table name include blanks, you need
square brackets around the name:

=DSum("Amt", "[Your Table]", "[Arr] = 2 And [colour]='red'")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

What if i want to add additional criteria...say [arr] =2 and
[colour]=red...

How to add it in your formula below....Thanks again

Try:

=DSum("Amt", "YourTable", "[Arr] = 2")

--
HTH
Van T. Dinh
MVP (Access)



I hv this records of data :

Arr Amt
---- -----
0 100
2 100
2 200
3 100

When i use this =Sum(Abs([Arr]=2)) i get 2 as result..

Q...How to sum the Amt where the criteria is same as above ctrl..
i expect the sum to be 300

Thanks