formula for balance & over expense amount

[aboiy]

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 Graesser]

Hi aboiy

Enter the following formula in cell D2 on sheet3

=SUMPRODUCT((Sheet1!$B$2:$B$100=B2)*(Sheet1!$C$2:$C$100)) o
=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 Graesse
(e-mail address removed)
Boston M

----- 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,90
5 - 050 8,40

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

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

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.
3- col.F - reflect only the "excess amount" from the
budget in col.
4- show blank " " instead of 0 when no expenses are
incurred for col.D,E,& F

For your kind assistance

Thanks and regards

aboi

 

[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
$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.

 

[Mark Graesser]

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 +

=389

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 Graesse
(e-mail address removed)
Boston M

----- 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,50
3- 247 1,500
4- 360 9
5- 050 0

Result should look like this

Sheet 3 (Summary Report
col. A B C D E
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,90
5 - 050 8,40

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

For your usual cooperation

Thanks and regards

aboi

 

[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.

 

[Mark Graesser]

Hi Aboiy
There are two ways to hide the zeros

1) You can go to Tools>Options>View 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 Graesse
(e-mail address removed)
Boston M


----- 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

aboi

-----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 bevalue 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 zeroThese two arrays are then multiplied and the components
added up

 

[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 Tools>Options>View 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.

 

[Mark Graesser]

Hi Aboiy
The mutliplier operator (*) inside the SUMPRODUCT function requires that you don't have any text the range in column C. If you have any text, which includes spaces or formulas which returns "", you will get the #VALUE error

You can avoid this by changing the formula to

=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))

However, if you have a number which is formatted as text, it may not be included in the sum.

If you are still having trouble you can e-mail me your workbook and I will be happy to take a look at it

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

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


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

aboi

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

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