By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,258 Members | 1,679 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,258 IT Pros & Developers. It's quick & easy.

How to make a condition for making a SUM - Correction

P: n/a
Hello

Is there an alternative for the Excel-function "SumIF" in Access?
Why?

I have one table with profits and expenses.
First field, description (text): indication like "Expense" or "Profit"
Second field (numerical): the amount

It's easy to make a total and sub totals in a report. Even the wizard can do
this.

Description Amount
Income A 100
Income B 100
Income C 100
Sum(Amount) 300

Expense A 100
Expense B 100
Sum(Amount) 200

Sum(Amount) 500 That's what I get right now.

What I like to compute: Sum(Income) minus Sum(Expense).
So the result should be 100 instead of 500.

How to make the calculation "Income" minus "Expense"?

Thanks !!!

Dirk Goossens
Office: +32 55 237609
Mobile: +32 495 52 35 90
Vrij Instituut Sint-Lucas
Gelukstede 2 - BE- 9700 Oudenaarde
Dec 1 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

Yes.
Because Access is a database, not a spreadsheet.

In Jet SQL, you could use either the IIF function or the Switch
function.

On Thu, 1 Dec 2005 11:01:41 +0100, "Dirk Goossens" <di*****@skynet.be>
wrote:
Is there an alternative for the Excel-function "SumIF" in Access?
Why?
I have one table with profits and expenses.
First field, description (text): indication like "Expense" or "Profit"
Second field (numerical): the amount
It's easy to make a total and sub totals in a report. Even the wizard can do
this.
Description Amount
Income A 100
Income B 100
Income C 100
Sum(Amount) 300
Expense A 100
Expense B 100
Sum(Amount) 200
Sum(Amount) 500 That's what I get right now.
What I like to compute: Sum(Income) minus Sum(Expense).
So the result should be 100 instead of 500.
How to make the calculation "Income" minus "Expense"?

--
Drive C: Error. (A)bort (R)etry (S)mack The Darned Thing

Dec 1 '05 #2

P: n/a

"Dirk Goossens" <di*****@skynet.be> wrote in message
news:43***********************@news.skynet.be...
Hello

Is there an alternative for the Excel-function "SumIF" in Access?
Why?

I have one table with profits and expenses.
First field, description (text): indication like "Expense" or "Profit"
Second field (numerical): the amount

It's easy to make a total and sub totals in a report. Even the wizard can do this.

Description Amount
Income A 100
Income B 100
Income C 100
Sum(Amount) 300

Expense A 100
Expense B 100
Sum(Amount) 200

Sum(Amount) 500 That's what I get right now.

What I like to compute: Sum(Income) minus Sum(Expense).
So the result should be 100 instead of 500.

How to make the calculation "Income" minus "Expense"?

Thanks !!!

Dirk Goossens
Office: +32 55 237609
Mobile: +32 495 52 35 90
Vrij Instituut Sint-Lucas
Gelukstede 2 - BE- 9700 Oudenaarde


sum(iif([indication]="Profit",[Amount],-[Amount]))
Dec 1 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.