CountIf & SumIf

[Maz Ten]

I am trying to count a certain field on a form/report on the condition it
contains the text "Yes" is there a function in Access like the function in
Excel
CountIf and SumIf
Any help would be appreciated

Maz Ten

 

[Fredg]

You really haven't given enough information.
Are you wishing to count or sum all records in a table?
Or just records shown in a report?

You did say a Text "Yes", not a numeric Yes value, didn't you?

All in the table use Domain Aggregate functions:
=DCount("*","TableName","[FieldName] = 'Yes'")
=DSum("[NumberField]","TableName","[FieldName] = 'Yes'")

Just records shown in the report:
To Count!!!
= Sum(IIf([SomeField] = "Yes",1,0))

To Sum
= Sum(IIf([SomeField] = "Yes",[NumberField],0))

 

[Maz Ten]

Thanks Fred this is exactly what I need
Maz Ten

You really haven't given enough information.
Are you wishing to count or sum all records in a table?
Or just records shown in a report?

You did say a Text "Yes", not a numeric Yes value, didn't you?

All in the table use Domain Aggregate functions:
=DCount("*","TableName","[FieldName] = 'Yes'")
=DSum("[NumberField]","TableName","[FieldName] = 'Yes'")

Just records shown in the report:
To Count!!!
= Sum(IIf([SomeField] = "Yes",1,0))

To Sum
= Sum(IIf([SomeField] = "Yes",[NumberField],0))

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

I am trying to count a certain field on a form/report on the condition it
contains the text "Yes" is there a function in Access like the function in
Excel
CountIf and SumIf
Any help would be appreciated

Maz Ten

 

[Maz Ten]

Thanks Hugh
Maz

Look at the DCount() and DSum() functions for this. They operate on a
'domain' of records which can be filtered by criteria in the function.

hth

Hugh