Sumif in Access?

[Paula]

Is there a function that works like sumif in Excel. I have some data I would
like to calculate in a report based on the information in a field. Can I do
that? Or do I have to go back to the query and use several different columns
to extract the information I need? That really won't achieve the results
that I want.

 

[Duane Hookom]

To sum the sales (quantity * unit cost) of red items:
=Abs(Sum([Color]="red" * [Qty] * [UnitCost]))
[Color]="red" is your if condition.

 

[Paula]

Ok, what if I just want add up the "unit cost" if the color is "red"?

To sum the sales (quantity * unit cost) of red items:
=Abs(Sum([Color]="red" * [Qty] * [UnitCost]))
[Color]="red" is your if condition.

--
Duane Hookom
MS Access MVP
--

Is there a function that works like sumif in Excel. I have some data I
would
like to calculate in a report based on the information in a field. Can I
do
that? Or do I have to go back to the query and use several different
columns
to extract the information I need? That really won't achieve the results
that I want.

 

[Paula]

What if I just want to total the "unit" if the color is "red"?

To sum the sales (quantity * unit cost) of red items:
=Abs(Sum([Color]="red" * [Qty] * [UnitCost]))
[Color]="red" is your if condition.

--
Duane Hookom
MS Access MVP
--

Is there a function that works like sumif in Excel. I have some data I
would
like to calculate in a report based on the information in a field. Can I
do
that? Or do I have to go back to the query and use several different
columns
to extract the information I need? That really won't achieve the results
that I want.

 

[Duane Hookom]

=Abs(Sum([Color]="red" * [UnitCost]))

--
Duane Hookom
MS Access MVP

Ok, what if I just want add up the "unit cost" if the color is "red"?

To sum the sales (quantity * unit cost) of red items:
=Abs(Sum([Color]="red" * [Qty] * [UnitCost]))
[Color]="red" is your if condition.

--
Duane Hookom
MS Access MVP
--

Is there a function that works like sumif in Excel. I have some data I
would
like to calculate in a report based on the information in a field. Can I
do
that? Or do I have to go back to the query and use several different
columns
to extract the information I need? That really won't achieve the results
that I want.

 

[Marshall Barton]

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))

 

[Duane Hookom]

Good catch Marsh.

--
Duane Hookom
MS Access MVP

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))

 

[Paula]

Thank you that works, but how do I do the other part? If the color is not
"red", I want it to be blank. In Excel I would write =sumif(A1=C3,D2-A4," ")
Can that be done in Access?

Good catch Marsh.

--
Duane Hookom
MS Access MVP

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))

 

[Duane Hookom]

What would cause the blank? No red color records? This would show a 0 with
this expression. You can set the format property of the text box to display
" " in the event that the text box value is 0.

--
Duane Hookom
MS Access MVP
--

Thank you that works, but how do I do the other part? If the color is not
"red", I want it to be blank. In Excel I would write =sumif(A1=C3,D2-A4,"
")
Can that be done in Access?

Good catch Marsh.

--
Duane Hookom
MS Access MVP

Duane Hookom wrote:

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))

 

[zyus]

What if i want to add another condition say [size]=7, How to put it in the
formula ...

If i want to SUM or COUNT..how should i go about it..

Thanks

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))

 

[Duane Hookom]

=Abs(Sum(([Color]="red" And [Size]=7) * [UnitCost]))

--
Duane Hookom
MS Access MVP

What if i want to add another condition say [size]=7, How to put it in
the
formula ...

If i want to SUM or COUNT..how should i go about it..

Thanks

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))

 

[zyus]

What if i want to make some excpetion..

Say [Colour]="red" and [size]=7 except [material]=non cotton....meaning
cotton material is included..

Thanks

=Abs(Sum(([Color]="red" And [Size]=7) * [UnitCost]))

--
Duane Hookom
MS Access MVP

What if i want to add another condition say [size]=7, How to put it in
the
formula ...

If i want to SUM or COUNT..how should i go about it..

Thanks

Duane Hookom wrote:

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))

 

[zyus]

cotton , silk ,& other material is included...

What if i want to make some excpetion..

Say [Colour]="red" and [size]=7 except [material]=non cotton....meaning
cotton material is included..

Thanks

=Abs(Sum(([Color]="red" And [Size]=7) * [UnitCost]))

--
Duane Hookom
MS Access MVP

What if i want to add another condition say [size]=7, How to put it in
the
formula ...

If i want to SUM or COUNT..how should i go about it..

Thanks



:

Duane Hookom wrote:

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))

 

[Duane Hookom]

I'm not sure where this will stop. When you get beyond a couple expressions,
consider creating lookup tables to find values or create a small
user-defined function.

--
Duane Hookom
MS Access MVP

cotton , silk ,& other material is included...

What if i want to make some excpetion..

Say [Colour]="red" and [size]=7 except [material]=non cotton....meaning
cotton material is included..

Thanks

=Abs(Sum(([Color]="red" And [Size]=7) * [UnitCost]))

--
Duane Hookom
MS Access MVP

What if i want to add another condition say [size]=7, How to put it
in
the
formula ...

If i want to SUM or COUNT..how should i go about it..

Thanks



:

Duane Hookom wrote:

=Abs(Sum([Color]="red" * [UnitCost]))

I think you need another set of parenthesis in there:

=Abs(Sum(([Color]="red") * [UnitCost]))