SUMIF calculations in Report

[Guest]

hi! desperate...can anyone help me? I'm creating a report and want to look at
2 different totals. 1 is a total of everything (which i figured out), but the
other that I want to add is a total of only certain groups. i guess i'm
imaging what i would do in excel, but can i do something like an excel SUMIF
function? like =SUMIF([Account Name]= "Name 1" or "Name 2" or Name 3") ? I
hope this makes sense...and i hope there is someone out there than can help
me! Thanks in advance!

 

[Guest]

Hi

=Sum(IIf([Account Name]="Name 1" or "Name 2" or Name 3",[XXX]))

[XXX] is the field you want to sum

 

[Guest]

Hi Wayne: Thanks so much for the speedy response! But it's not working. To be
a little more specific, i am basically adding up #s that are already count
calculations. Does that matter? And I'm putting it in the report
footer...and for some reason, even though i put in the restrictions or
[Account Name}="N1" or "N2" or ..., it's adding up everything on the report.
Does this make sense?

So, let me try explaining again. I have 4 different "groups" and within each
group there are sub-groups. Within each "group" section, I have a total,
which is a sum of the counts for each sub-group (data pulled from query).
Then at the very end of the report in the report footer, i have a Grand Total
sum which is the total sum of each of the 4 group totals. But i want to add
another sub-grand total (i guess that's what you'd call it) of just 3 out of
the 4 groups totals - so basically omit 1 of them. Is this possible with the
SUMIF function?

~and just curious...I noticed in Access that everything always has 2 "I"s in
front of the fuction...why is this? like Sum(IIf)...

Thanks for your help.

Hi

=Sum(IIf([Account Name]="Name 1" or "Name 2" or Name 3",[XXX]))

[XXX] is the field you want to sum

--
Wayne
Manchester, England.

hi! desperate...can anyone help me? I'm creating a report and want to look at
2 different totals. 1 is a total of everything (which i figured out), but the
other that I want to add is a total of only certain groups. i guess i'm
imaging what i would do in excel, but can i do something like an excel SUMIF
function? like =SUMIF([]= "Name 1" or "Name 2" or Name 3") ? I
hope this makes sense...and i hope there is someone out there than can help
me! Thanks in advance!

 

[Douglas J. Steele]

Sorry, Wayne, that's incorrect. You need to repeat the field name for each
value being checked:

=Sum(IIf([Account Name]="Name 1" or [Account Name]="Name 2" or [Account
Name]="Name 3",[XXX], 0))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

=Sum(IIf([Account Name]="Name 1" or "Name 2" or Name 3",[XXX]))

[XXX] is the field you want to sum

--
Wayne
Manchester, England.

hi! desperate...can anyone help me? I'm creating a report and want to
look at
2 different totals. 1 is a total of everything (which i figured out), but
the
other that I want to add is a total of only certain groups. i guess i'm
imaging what i would do in excel, but can i do something like an excel
SUMIF
function? like =SUMIF([]= "Name 1" or "Name 2" or Name 3") ? I
hope this makes sense...and i hope there is someone out there than can
help
me! Thanks in advance!

 

[Guest]

Hi

To start with I do not have a clue why access spells if "iif". There must
be a reason though. Anyway You can use IIF for in access for only 2 things.
Something either is true or it's not. Like this

=IIf([Today]="Christmas Day",Something,Something Else)
This would be the contents of the field or control called Today contain the
text Christmas Day. If this was true you would have Something if not you
would have something else.

You can expand on this by saying something like
=Sum(IIf([PayDate]>#01/01/2000# And [PaySource]="Wayne",[PAYAmount]))
This would give you a sum total of the PayAmount field if it was true the
the PayDate was made after 1st Jan 2000 and the PaySource field contained the
Text Wayne.

You problem (as far as I can see) it like this
You want a grand total of something but you want to omit something else.
So try something like this (can't be very specific as can't see your report)
=Sum(IIf([AcountName]="N1" Or "N2" Or "N3",[XXX]))
Or
=Count(IIf([AcountName]="N1" Or "N2" Or "N3",[XXX]))
Note this means that the "contents" of the field call AcountName should
contain the text N1 or N2 or N3 these are not field names.

To give a specific answer you would need to post all the field names.


--
Wayne
Manchester, England.

Hi Wayne: Thanks so much for the speedy response! But it's not working. To be
a little more specific, i am basically adding up #s that are already count
calculations. Does that matter? And I'm putting it in the report
footer...and for some reason, even though i put in the restrictions or
[Account Name}="N1" or "N2" or ..., it's adding up everything on the report.
Does this make sense?

So, let me try explaining again. I have 4 different "groups" and within each
group there are sub-groups. Within each "group" section, I have a total,
which is a sum of the counts for each sub-group (data pulled from query).
Then at the very end of the report in the report footer, i have a Grand Total
sum which is the total sum of each of the 4 group totals. But i want to add
another sub-grand total (i guess that's what you'd call it) of just 3 out of
the 4 groups totals - so basically omit 1 of them. Is this possible with the
SUMIF function?

~and just curious...I noticed in Access that everything always has 2 "I"s in
front of the fuction...why is this? like Sum(IIf)...

Thanks for your help.

Hi

=Sum(IIf([Account Name]="Name 1" or "Name 2" or Name 3",[XXX]))

[XXX] is the field you want to sum

--
Wayne
Manchester, England.

hi! desperate...can anyone help me? I'm creating a report and want to look at
2 different totals. 1 is a total of everything (which i figured out), but the
other that I want to add is a total of only certain groups. i guess i'm
imaging what i would do in excel, but can i do something like an excel SUMIF
function? like =SUMIF([]= "Name 1" or "Name 2" or Name 3") ? I
hope this makes sense...and i hope there is someone out there than can help
me! Thanks in advance!

 

[Guest]

Hi Golly

I have just checked Douglas's post and he is of course correct. You need to
put the field name with each criteria.
[Account Name]="Name 1" or [Account Name]="Name 2" etc etc

Cheers Douglas - I will get the hang of this access thing one day // LoL

--
Wayne
Manchester, England.

Sorry, Wayne, that's incorrect. You need to repeat the field name for each
value being checked:

=Sum(IIf([Account Name]="Name 1" or [Account Name]="Name 2" or [Account
Name]="Name 3",[XXX], 0))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

=Sum(IIf([Account Name]="Name 1" or "Name 2" or Name 3",[XXX]))

[XXX] is the field you want to sum

--
Wayne
Manchester, England.

hi! desperate...can anyone help me? I'm creating a report and want to
look at
2 different totals. 1 is a total of everything (which i figured out), but
the
other that I want to add is a total of only certain groups. i guess i'm
imaging what i would do in excel, but can i do something like an excel
SUMIF
function? like =SUMIF([]= "Name 1" or "Name 2" or Name 3") ? I
hope this makes sense...and i hope there is someone out there than can
help
me! Thanks in advance!

 

[Guest]

Wow...you guys are seriously the best. Thanks so much for your help! And
yes...Douglas, you were correct.

....just curious...are you guys always on to answer people's questions? I
have SO MUCH more to learn in Access...it's exciting that there is this
forum. This is my first time posting, and I'm thrilled with the knowledge &
response!

Sorry, Wayne, that's incorrect. You need to repeat the field name for each
value being checked:

=Sum(IIf([Account Name]="Name 1" or [Account Name]="Name 2" or [Account
Name]="Name 3",[XXX], 0))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi

=Sum(IIf([Account Name]="Name 1" or "Name 2" or Name 3",[XXX]))

[XXX] is the field you want to sum

--
Wayne
Manchester, England.

hi! desperate...can anyone help me? I'm creating a report and want to
look at
2 different totals. 1 is a total of everything (which i figured out), but
the
other that I want to add is a total of only certain groups. i guess i'm
imaging what i would do in excel, but can i do something like an excel
SUMIF
function? like =SUMIF([]= "Name 1" or "Name 2" or Name 3") ? I
hope this makes sense...and i hope there is someone out there than can
help
me! Thanks in advance!

 

[Guest]

Wayne:

When I first encountered it, back in the days before Doris Day was a virgin,
it was usually documented as standing for Immediate If. Strictly speaking,
as well as returning values on the basis of an expression being TRUE or NOT
TRUE (FALSE) it also returns a value if it IS NULL (which is not the same as
NOT TRUE or FALSE).

? IIF(NULL = NOT TRUE,1,2)

? IIF(NULL = TRUE,1,2)

? IIF(NULL = NOT FALSE,1,2)

? IIF(NULL = FALSE,1,2)

? IIF(NULL=NULL,1,2)

? IIF(NULL,1,2)

All return 2, but on the other hand:

? IIF(IsNull(NULL = NOT TRUE),1,2)

and so on, would all return 1.

Ken Sheridan
Stafford, England

Hi

To start with I do not have a clue why access spells if "iif". There must
be a reason though. Anyway You can use IIF for in access for only 2 things.
Something either is true or it's not. Like this

=IIf([Today]="Christmas Day",Something,Something Else)
This would be the contents of the field or control called Today contain the
text Christmas Day. If this was true you would have Something if not you
would have something else.

You can expand on this by saying something like
=Sum(IIf([PayDate]>#01/01/2000# And [PaySource]="Wayne",[PAYAmount]))
This would give you a sum total of the PayAmount field if it was true the
the PayDate was made after 1st Jan 2000 and the PaySource field contained the
Text Wayne.

You problem (as far as I can see) it like this
You want a grand total of something but you want to omit something else.
So try something like this (can't be very specific as can't see your report)
=Sum(IIf([AcountName]="N1" Or "N2" Or "N3",[XXX]))
Or
=Count(IIf([AcountName]="N1" Or "N2" Or "N3",[XXX]))
Note this means that the "contents" of the field call AcountName should
contain the text N1 or N2 or N3 these are not field names.

To give a specific answer you would need to post all the field names.


--
Wayne
Manchester, England.

Hi Wayne: Thanks so much for the speedy response! But it's not working. To be
a little more specific, i am basically adding up #s that are already count
calculations. Does that matter? And I'm putting it in the report
footer...and for some reason, even though i put in the restrictions or
[Account Name}="N1" or "N2" or ..., it's adding up everything on the report.
Does this make sense?

So, let me try explaining again. I have 4 different "groups" and within each
group there are sub-groups. Within each "group" section, I have a total,
which is a sum of the counts for each sub-group (data pulled from query).
Then at the very end of the report in the report footer, i have a Grand Total
sum which is the total sum of each of the 4 group totals. But i want to add
another sub-grand total (i guess that's what you'd call it) of just 3 out of
the 4 groups totals - so basically omit 1 of them. Is this possible with the
SUMIF function?

~and just curious...I noticed in Access that everything always has 2 "I"s in
front of the fuction...why is this? like Sum(IIf)...

Thanks for your help.

Hi

=Sum(IIf([Account Name]="Name 1" or "Name 2" or Name 3",[XXX]))

[XXX] is the field you want to sum

--
Wayne
Manchester, England.



:

hi! desperate...can anyone help me? I'm creating a report and want to look at
2 different totals. 1 is a total of everything (which i figured out), but the
other that I want to add is a total of only certain groups. i guess i'm
imaging what i would do in excel, but can i do something like an excel SUMIF
function? like =SUMIF([]= "Name 1" or "Name 2" or Name 3") ? I
hope this makes sense...and i hope there is someone out there than can help
me! Thanks in advance!