formula for balance & over expense amount

Hi,

I have this spreadsheet for our daily expenses

Sheet 1 (Budget & Cost Center)
col. A B C
No. Acct. Budget
1- 360 4,600
2- 280 3,500
3- 247 1,200
4 - 315 7,900
5 - 050 8,400

Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247 1,500
4- 360 90
5- 050 0

Sheet 3 (Summary Report)
col. A B C D E F
No. Acct. Budget Expenses Balance Over Exp.
1- 360 4,600
2- 280 3,500
3- 247 1,200
4 - 315 7,900
5 - 050 8,400

I need to make a formula in sheet3
1- col.D - totalling the expenses in sheet2 base on the
account.
2- col.E - show only the balance "less than" the budget
in col.C
3- col.F - reflect only the "excess amount" from the
budget in col.C
4- show blank " " instead of 0 when no expenses are
incurred for col.D,E,& F.

For your kind assistance.

Thanks and regards.

aboiy

Hi aboiy,

Enter the following formula in cell D2 on sheet3:

=SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!$C$2:$ C$100)) or
=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!$C$ 2:$C$100))=0,"",SUMPRODUCT((Sheet1!$B$2:$B$100=B2) *(Sheet1!$C$2:$C$100)))

This will give you the total expenses on sheet 1 for the account number in coulmn B.

For question 2 use =D2-C2 or =IF(D2-C2=0,"",D2-C2)

For question 3 use =C2-D2 or =IF(C2-D2=0,"",C2-D2)

In all of the above cases the second formula will prevent a zero or negative value from showing up.


Good Luck,
Mark Graesser

Boston MA

----- aboiy wrote: -----

Hi,

I have this spreadsheet for our daily expenses

Sheet 1 (Budget & Cost Center)
col. A B C
No. Acct. Budget
1- 360 4,600
2- 280 3,500
3- 247 1,200
4 - 315 7,900
5 - 050 8,400

Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247 1,500
4- 360 90
5- 050 0

Sheet 3 (Summary Report)
col. A B C D E F
No. Acct. Budget Expenses Balance Over Exp.
1- 360 4,600
2- 280 3,500
3- 247 1,200
4 - 315 7,900
5 - 050 8,400

I need to make a formula in sheet3
1- col.D - totalling the expenses in sheet2 base on the
account.
2- col.E - show only the balance "less than" the budget
in col.C
3- col.F - reflect only the "excess amount" from the
budget in col.C
4- show blank " " instead of 0 when no expenses are
incurred for col.D,E,& F.

For your kind assistance.

Thanks and regards.

aboiy

Good day Mark,

I'm in the office now and trying to apply your formula,
is it possible to make reference on sheet 2 col."C"
wherein the expenses can be totalled based on the account
in col."B" the same account can be found in sheet 1 with
corresponding budget to follow.

Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247 1,500
4- 360 90
5- 050 0

Result should look like this:

Sheet 3 (Summary Report)
col. A B C D E F
No. Acct. Budget Expenses Balance Over Exp.
1- 360 4,600 3,890 710
2- 280 3,500
3- 247 1,200 9,000 7,800
4 - 315 7,900
5 - 050 8,400

Notice that in sheet 2
1 - item 1&4 having same acct. "360"
2 - item 2&3 having same acct. "247"

For your usual cooperation.

Thanks and regards.

aboiy

-----Original Message-----
Hi aboiy,

Enter the following formula in cell D2 on sheet3:

=SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!$C$2: $C$100))
or
=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!
$C$2:$C$100))=0,"",SUMPRODUCT((Sheet1!$B$2:$B$100= B2)*
(Sheet1!$C$2:$C$100)))

This will give you the total expenses on sheet 1 for the
account number in coulmn B.

For question 2 use =D2-C2 or =IF(D2-C2=0,"",D2-C2)

For question 3 use =C2-D2 or =IF(C2-D2=0,"",C2-D2)

In all of the above cases the second formula will prevent
a zero or negative value from showing up.


Good Luck,
Mark Graesser

Boston MA

----- aboiy wrote: -----

Hi,

I have this spreadsheet for our daily expenses

Sheet 1 (Budget & Cost Center)
col. A B C
No. Acct. Budget
1- 360 4,600
2- 280 3,500
3- 247 1,200
4 - 315 7,900
5 - 050 8,400

Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247
1,500
4- 360 90
5- 050 0

Sheet 3 (Summary Report)
col. A B C D E F
No. Acct. Budget Expenses Balance Over
Exp.
1- 360 4,600
2- 280 3,500
3- 247 1,200
4 - 315 7,900
5 - 050 8,400

I need to make a formula in sheet3
1- col.D - totalling the expenses in sheet2 base
on the
account.
2- col.E - show only the balance "less than" the
budget
in col.C
3- col.F - reflect only the "excess amount" from
the
budget in col.C
4- show blank " " instead of 0 when no expenses
are
incurred for col.D,E,& F.

For your kind assistance.

Thanks and regards.

aboiy




.

Mark,

Everything is clear to me now, but a small favor, can we
apply the same principle of showing blank if the expenses
equals to "0" for col. D.

Again thank you for your time and patience.

Thanks and regards.

aboiy




-----Original Message-----
Hi aboiy,
I see now that I used the wrong sheet names. When I put
the formula together I had the expenses on sheet 1. The
formula you need would be:

=SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2: $C$100))

You need to use a range because full column reference
(ex. B:B) won't work in the SUMPRODUCT function.

Basically the (Sheet2!$B$2:$B$100=B2) will compare the
value in sheet 2 column B with the value in cell B2 on
sheet 3. If they match it returns a 1, if not it returns
a zero.

The (Sheet2!$C$2:$C$100) returns the expense value.
These two arrays are then multiplied and the components
added up.

So in your example, with 360 in B2:

=SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2: $C$100))

=(1,0,0,1,0) * (3800, 7500, 1500, 90, 0)

=(1*3800) + (0*7500) + (0*1500) + (1*90) + (0*0)

=3800 + 0 + 0 + 90 + 0

=3890

When you copy this formula down it will give you the
totals for the other accounts.

Let me know if you have any trouble.

Good Luck,
Mark Graesser

Boston MA

----- Aboiy wrote: -----


Good day Mark,

I'm in the office now and trying to apply your
formula,
is it possible to make reference on sheet 2 col."C"
wherein the expenses can be totalled based on the
account
in col."B" the same account can be found in sheet 1
with
corresponding budget to follow.

Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247
1,500
4- 360 90
5- 050 0

Result should look like this:

Sheet 3 (Summary Report)
col. A B C D E F
No. Acct. Budget Expenses Balance
Over Exp.
1- 360 4,600 3,890 710
2- 280 3,500
3- 247 1,200 9,000
7,800
4 - 315 7,900
5 - 050 8,400

Notice that in sheet 2
1 - item 1&4 having same acct. "360"
2 - item 2&3 having same acct. "247"

For your usual cooperation.

Thanks and regards.

aboiy

-----Original Message-----
Hi aboiy,
Enter the following formula in cell D2 on sheet3:
=SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!
$C$2:$C$100))
or
=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!
$C$2:$C$100))=0,"",SUMPRODUCT((Sheet1!$B$2:$B$100= B2)
*
(Sheet1!$C$2:$C$100)))
This will give you the total expenses on sheet 1
for the
account number in coulmn B.
For question 2 use =D2-C2 or =IF(D2-C2=0,"",D2-C2)
For question 3 use =C2-D2 or =IF(C2-D2=0,"",C2-D2)
In all of the above cases the second formula will
prevent
a zero or negative value from showing up.
Good Luck,
Mark Graesser

Boston MA
----- aboiy wrote: -----
Hi,
I have this spreadsheet for our daily
expenses
Sheet 1 (Budget & Cost Center)
col. A B C
No. Acct. Budget
1- 360 4,600
2- 280 3,500
3- 247
1,200
4 - 315 7,900
5 - 050 8,400
Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247
1,500
4- 360 90
5- 050 0
Sheet 3 (Summary Report)
col. A B C D
E F
No. Acct. Budget Expenses Balance
Over
Exp.
1- 360 4,600
2- 280 3,500
3- 247
1,200
4 - 315 7,900
5 - 050 8,400
I need to make a formula in sheet3
1- col.D - totalling the expenses in sheet2
base
on the
account.
2- col.E - show only the balance "less than"
the
budget
in col.C
3- col.F - reflect only the "excess amount"
from
the
budget in col.C
4- show blank " " instead of 0 when no
expenses
are
incurred for col.D,E,& F.
For your kind assistance.
Thanks and regards.
aboiy
.

.

Hi Aboiy,
There are two ways to hide the zeros.

1) You can go to ToolsOptionsView and uncheck "Zero values". However, this will hide every zero on the sheet.

2) You can put your formula inside an IF statment. The If statement would be set up as:
=IF(yourformula=0,"",yourformula)
This setup can create some long formulas, but it allows you to hide the zeros values you want to, while still displaying other zero values.

So for your sample the formula would be:

=IF(SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$ 2:$C$100))=0,"",SUMPRODUCT((Sheet2!$B$2:$B$100=B2) *(Sheet2!$C$2:$C$100)))

Glad to hear that I have been helpful.

Good Luck,
Mark Graesser

Boston MA


----- Aboiy wrote: -----


Mark,

Everything is clear to me now, but a small favor, can we
apply the same principle of showing blank if the expenses
equals to "0" for col. D.

Again thank you for your time and patience.

Thanks and regards.

aboiy




-----Original Message-----
Hi aboiy,
I see now that I used the wrong sheet names. When I put
the formula together I had the expenses on sheet 1. The
formula you need would be:
=SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2 :$C$100))
You need to use a range because full column reference
(ex. B:B) won't work in the SUMPRODUCT function.
Basically the (Sheet2!$B$2:$B$100=B2) will compare the
value in sheet 2 column B with the value in cell B2 on
sheet 3. If they match it returns a 1, if not it returns
a zero.
The (Sheet2!$C$2:$C$100) returns the expense value.
These two arrays are then multiplied and the components
added up.
So in your example, with 360 in B2:
=SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!$C$2 :$C$100))
=(1,0,0,1,0) * (3800, 7500, 1500, 90, 0)
=(1*3800) + (0*7500) + (0*1500) + (1*90) + (0*0)
=3800 + 0 + 0 + 90 + 0
=3890
When you copy this formula down it will give you the
totals for the other accounts.
Let me know if you have any trouble.
Good Luck,
Mark Graesser

Boston MA
----- Aboiy wrote: -----
Good day Mark,
I'm in the office now and trying to apply your
formula,
is it possible to make reference on sheet 2 col."C"
wherein the expenses can be totalled based on the
account
in col."B" the same account can be found in sheet 1
with
corresponding budget to follow.
Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247
1,500
4- 360 90
5- 050 0
Result should look like this:
Sheet 3 (Summary Report)
col. A B C D E F
No. Acct. Budget Expenses Balance
Over Exp.
1- 360 4,600 3,890 710
2- 280 3,500
3- 247 1,200 9,000
7,800
4 - 315 7,900
5 - 050 8,400
Notice that in sheet 2
1 - item 1&4 having same acct. "360"
2 - item 2&3 having same acct. "247"
For your usual cooperation.
Thanks and regards.
aboiy
-----Original Message-----
Hi aboiy,
Enter the following formula in cell D2 on sheet3:
=SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!
$C$2:$C$100))
or
=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1 !
$C$2:$C$100))=0,"",SUMPRODUCT((Sheet1!$B$2:$B$100= B2)
*
(Sheet1!$C$2:$C$100)))
This will give you the total expenses on sheet 1
for the
account number in coulmn B.
For question 2 use =D2-C2 or =IF(D2-C2=0,"",D2-C2)
For question 3 use =C2-D2 or =IF(C2-D2=0,"",C2-D2)
In all of the above cases the second formula will
prevent
a zero or negative value from showing up.
Good Luck,
Mark Graesser

Boston MA
----- aboiy wrote: -----
Hi,
I have this spreadsheet for our daily
expenses
Sheet 1 (Budget & Cost Center)
col. A B C
No. Acct. Budget
1- 360 4,600
2- 280 3,500
3- 247
1,200
4 - 315 7,900
5 - 050 8,400
Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247
1,500
4- 360 90
5- 050 0
Sheet 3 (Summary Report)
col. A B C D
E F
No. Acct. Budget Expenses Balance
Over
Exp.
1- 360 4,600
2- 280 3,500
3- 247
1,200
4 - 315 7,900
5 - 050 8,400
I need to make a formula in sheet3
1- col.D - totalling the expenses in sheet2
base
on the
account.
2- col.E - show only the balance "less than"
the
budget
in col.C
3- col.F - reflect only the "excess amount"
from
the
budget in col.C
4- show blank " " instead of 0 when no
expenses
are
incurred for col.D,E,& F.
For your kind assistance.
Thanks and regards.
aboiy
.
.

Mark,

When i put the formula in column D "Expense" column, rows
with a blank result on it will show a "#value!" symbols
appear both in the balance & over expense columns.

No. Acct. Budget Expenses Balance Over Exp.
1- 360 4,600 3,890 710
2- 280 3,500 #VALUE! #VALUE!
3- 247 1,200 9,000 7,800
4- 315 7,900 #VALUE! #VALUE!
5 - 050 8,400 #VALUE! #VALUE!

Is there quick fix for this problem.

This is the formula in col.E "Balance" column:
=IF(C3-D3=0,"",C3-D3)
while in col.F "Over Expense" column:
=IF(D3-C3=0,"",D3-C3)

Regards,

aboiy




-----Original Message-----
Hi Aboiy,
There are two ways to hide the zeros.

1) You can go to ToolsOptionsView and uncheck "Zero
values". However, this will hide every zero on the sheet.

2) You can put your formula inside an IF statment. The
If statement would be set up as:
=IF(yourformula=0,"",yourformula)
This setup can create some long formulas, but it allows
you to hide the zeros values you want to, while still
displaying other zero values.

So for your sample the formula would be:

=IF(SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!
$C$2:$C$100))=0,"",SUMPRODUCT((Sheet2!$B$2:$B$100= B2)*
(Sheet2!$C$2:$C$100)))

Glad to hear that I have been helpful.

Good Luck,
Mark Graesser

Boston MA


----- Aboiy wrote: -----


Mark,

Everything is clear to me now, but a small favor,
can we
apply the same principle of showing blank if the
expenses
equals to "0" for col. D.

Again thank you for your time and patience.

Thanks and regards.

aboiy




-----Original Message-----
Hi aboiy,
I see now that I used the wrong sheet names. When
I put
the formula together I had the expenses on sheet 1.
The
formula you need would be:
=SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!
$C$2:$C$100))
You need to use a range because full column
reference
(ex. B:B) won't work in the SUMPRODUCT function.
Basically the (Sheet2!$B$2:$B$100=B2) will
compare the
value in sheet 2 column B with the value in cell B2
on
sheet 3. If they match it returns a 1, if not it
returns
a zero.
The (Sheet2!$C$2:$C$100) returns the expense
value.
These two arrays are then multiplied and the
components
added up.
So in your example, with 360 in B2:
=SUMPRODUCT((Sheet2!$B$2:$B$100=B2)*(Sheet2!
$C$2:$C$100))
=(1,0,0,1,0) * (3800, 7500, 1500, 90, 0)
=(1*3800) + (0*7500) + (0*1500) + (1*90) + (0*0)
=3800 + 0 + 0 + 90 + 0
=3890
When you copy this formula down it will give you
the
totals for the other accounts.
Let me know if you have any trouble.
Good Luck,
Mark Graesser

Boston MA
----- Aboiy wrote: -----
Good day Mark,
I'm in the office now and trying to apply
your
formula,
is it possible to make reference on sheet 2
col."C"
wherein the expenses can be totalled based on
the
account
in col."B" the same account can be found in
sheet 1
with
corresponding budget to follow.
Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247
1,500
4- 360 90
5- 050 0
Result should look like this:
Sheet 3 (Summary Report)
col. A B C D
E F
No. Acct. Budget Expenses
Balance
Over Exp.
1- 360 4,600 3,890
710
2- 280 3,500
3- 247 1,200
9,000
7,800
4 - 315 7,900
5 - 050 8,400
Notice that in sheet 2
1 - item 1&4 having same acct. "360"
2 - item 2&3 having same acct. "247"
For your usual cooperation.
Thanks and regards.
aboiy
-----Original Message-----
Hi aboiy,
Enter the following formula in cell D2 on sheet3:
=SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!
$C$2:$C$100))
or
=IF(SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1 !
$C$2:$C$100))=0,"",SUMPRODUCT((Sheet1!
$B$2:$B$100=B2)
*
(Sheet1!$C$2:$C$100)))
This will give you the total expenses on sheet 1
for the
account number in coulmn B.
For question 2 use =D2-C2 or =IF(D2-C2=0,"",D2-
C2)
For question 3 use =C2-D2 or =IF(C2-D2=0,"",C2-
D2)
In all of the above cases the second formula will
prevent
a zero or negative value from showing up.
Good Luck,
Mark Graesser

Boston MA
----- aboiy wrote: -----
Hi,
I have this spreadsheet for our daily
expenses
Sheet 1 (Budget & Cost Center)
col. A B C
No. Acct. Budget
1- 360 4,600
2- 280 3,500
3- 247
1,200
4 - 315 7,900
5 - 050 8,400
Sheet 2 (Daily Expenses)
col. A B C
No. Acct. Expenses
1- 360 3,800
2- 247 7,500
3- 247
1,500
4- 360 90
5- 050 0
Sheet 3 (Summary Report)
col. A B C D
E F
No. Acct. Budget Expenses Balance
Over
Exp.
1- 360 4,600
2- 280 3,500
3- 247
1,200
4 - 315 7,900
5 - 050 8,400
I need to make a formula in sheet3
1- col.D - totalling the expenses in sheet2
base
on the
account.
2- col.E - show only the balance "less
than"
the
budget
in col.C
3- col.F - reflect only the "excess amount"
from
the
budget in col.C
4- show blank " " instead of 0 when no
expenses
are
incurred for col.D,E,& F.
For your kind assistance.
Thanks and regards.
aboiy
.
.

.