Sum If

E

EmoryKA

Is there a function in access similar to the sumif function in Excel?
Example - If I have 3 fields [A], , and [C], then I would like to sum the
values of [A] if or [C] is not equal to zero.
 
K

KARL DEWEY

Will not work like you expressed as there is a single number for [A] in your
example, therefore nothing to sum. You can sum [A] & if [C] is not equal
to zero.

Iif([C]<>0, [A]+,0)
 
E

EmoryKA

Karl:
My fields are in a report. The actual names are "Cubes", "Pick Start", and
"Pick End'.
What I want to do (on the report footer) is to sum the "Cubes" if the "Pick
End" is not equal to 0.

I have tried =IIf([Pick End]<>0,Sum([Cubes]),0) and this returns the sum of
all "Cubes" instead of just the ones that have a value in the "Pick End".

KARL DEWEY said:
Will not work like you expressed as there is a single number for [A] in your
example, therefore nothing to sum. You can sum [A] & if [C] is not equal
to zero.

Iif([C]<>0, [A]+,0)

EmoryKA said:
Is there a function in access similar to the sumif function in Excel?
Example - If I have 3 fields [A], , and [C], then I would like to sum the
values of [A] if or [C] is not equal to zero.
Click to expand...
Click to expand...
 
P

peregenem

KARL said:
Will not work like you expressed as there is a single number for [A] in your
example, therefore nothing to sum. You can sum [A] & if [C] is not equal
to zero.

Iif([C]<>0, [A]+,0)
Click to expand...


I think the OP meant that A, B and C are columns of a set

SELECT SUM([A]) AS sum_if_A
FROM MyTable
WHERE <> 0 OR [C] <> 0
 
E

EmoryKA

Yes. This is in a text box on the report footer. The goal is to determine
how many cubes have been completed, and how many cubes are in process.
 
D

Dirk Goldgar

EmoryKA said:
Karl:
My fields are in a report. The actual names are "Cubes", "Pick
Start", and "Pick End'.
What I want to do (on the report footer) is to sum the "Cubes" if the
"Pick End" is not equal to 0.

I have tried =IIf([Pick End]<>0,Sum([Cubes]),0) and this returns the
sum of all "Cubes" instead of just the ones that have a value in the
"Pick End".
Click to expand...

You might try

=Sum(IIf([Pick End]<>0, [Cubes], 0))
 
E

EmoryKA

Mr. Goldar:
Thank you. It worked.

Dirk Goldgar said:
EmoryKA said:
Karl:
My fields are in a report. The actual names are "Cubes", "Pick
Start", and "Pick End'.
What I want to do (on the report footer) is to sum the "Cubes" if the
"Pick End" is not equal to 0.

I have tried =IIf([Pick End]<>0,Sum([Cubes]),0) and this returns the
sum of all "Cubes" instead of just the ones that have a value in the
"Pick End".
Click to expand...

You might try

=Sum(IIf([Pick End]<>0, [Cubes], 0))

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top