SumIf Formula with Multiple Criteria

[lindz5970]

I'm trying to create a Sumif formula that contains multiple 'ifs'. I was able to do this using the formula I pasted below but my "Type" field has grown to be too many different options and now my expression is too long for Access to handle so I need to consolidate this somehow while still maintaining the same result.

Original formula:
Sum(IIf([retro count]<>0 And [contract]="S" And [Type]="ABC",[retro count],0))+Sum(IIf([retro count]<>0 And [contract]="S" And [Type]="DEF",[retro count],0))+Sum(IIf([retro count]<>0 And [contract]="S" And [Type]="GHI",[retro count],0+Sum(IIf([retro count]<>0 And [contract]="S" And [Type]="JKL",[retro count],0))....

The underlined part is essentially the whole expression. I just kept adding them together, copying the expression and just changing the "Type" (I have about 20 "Types" now)

I tried adding an "Or" and listing the multiple types but it only worked when I had one Or. When I added more than 2 Ors it errored out:
Sum(IIf([retro count]<>0 And [contract]="S" And [Type]="ABC" or [Type]="DEF",[retro count],0))

I'm sure there is a simpler way to accomplish what I'm trying to do but can't figure it out. Any help would be greatly appreciated, Thanks!

 

[xenou]

These are equivalent, and you can see that the latter is shorter.

[Type] in ('ABC' or [Type] in 'DEF' or [Type] in 'GHI'

[Type] in ('ABC','DEF','GHI')

 

[lindz5970]

I've never used "in" so maybe I'm not doing it right because I still got an error message (it displays #Error )
Is this right?

Sum(IIf([retro count]<>0 And [contract]="S" And [Type] in
("ABC","DEF","GHI","JLK","MNO","PQR","STU","VWX"),[retro count],0))

 

[xenou]

No syntax problems (except that this is incomplete - there is no field name alias or even a table so technically as is it can't run). You should be sure you have all values in [retro count] that can be summed or you would get an error because of the data.

For what it's worth you can write plain vanilla SQL instead (personally I almost never use IIF):

SELECT Sum([retro count]) as SumOfRetroCount
FROM Table1
WHERE
	[retro count]<>0 
	And [contract]="S"
	And [Type] in ("ABC","DEF","GHI","JLK","MNO","PQR","STU","VWX")

 

[lindz5970]

No syntax problems (except that this is incomplete - there is no field name alias or even a table so technically as is it can't run). You should be sure you have all values in [retro count] that can be summed or you would get an error because of the data.

For what it's worth you can write plain vanilla SQL instead (personally I almost never use IIF):

SELECT Sum([retro count]) as SumOfRetroCount
FROM Table1
WHERE
    [retro count]<>0 
    And [contract]="S"
    And [Type] in ("ABC","DEF","GHI","JLK","MNO","PQR","STU","VWX")

The formula is being used in a text box in a customized Report so I don't think I can use SQL there. Unless there's a way to put SQL in a text box? but I thought I could only build an expression there...

 

[Joe4]

You should be able to use the DSUM function as the data source of your text box. The formula would look something like this:

DSum("[retro count]","[Table1]","[contract]='S' AND [type] in ('ABC','DEF','GHI','JLK','MNO','PQR','STU','VWX')")

Note that there is no reason for criteria checking to see if "retro count" is other than zero, as adding zero to the Sum does not change it!

For more on DSUM, read this:
https://theaccessbuddy.wordpress.com/2013/12/25/dsum-function-the-art-of-writing-a-criteria/

 

[lindz5970]

You should be able to use the DSUM function as the data source of your text box. The formula would look something like this:

DSum("[retro count]","[Table1]","[contract]='S' AND [type] in ('ABC','DEF','GHI','JLK','MNO','PQR','STU','VWX')")

Note that there is no reason for criteria checking to see if "retro count" is other than zero, as adding zero to the Sum does not change it!

For more on DSUM, read this:
https://theaccessbuddy.wordpress.com/2013/12/25/dsum-function-the-art-of-writing-a-criteria/

That was very helpful to shorten my expression however I'm still getting #Error . To test this, I deleted everything after the "AND" so it was only using the one criteria and that worked just fine so I believe it's something with the "in" part of the expression. Is there another way to list multiple 'or' criteria?

 

[xenou]

That should have worked. Can you post your expression now? Also post a sample record with one of those Types from the in clause - maybe it's a data type error.

 

[Micron]

#Error is what you'd get if
- the field or table name was not correct
- if they required square brackets but were missing
- if a quote is missing
#Name is what you'd get if you forgot the = sign in front of the expression in the control.

Type is not a word I would use to name any db object. Maybe keep this bookmark handy for words you shouldn't use.
http://allenbrowne.com/AppIssueBadWord.html

 

[lindz5970]

I figured it out, when I copy and pasted from here the quotes were in a weird format that access was not recognizing. once I re-typed it access manually, it worked. thanks for all your help!!