Need formulas for accounting spreadsheets
Closed
ikanikon

Apr 19, 2010 at 09:20 AM
rizvisa1
rizvisa1
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Related:
 Need formulas for accounting spreadsheets
 What is a formula on a spreadsheet  Articles
 How to transfer a formula from one spreadsheet to another  Guide
 Excel 2010VB Macro formula into many spreadsheets ✓  Forum  Excel
 Update dates in Excel spreadsheet: each year, formula  Guide
 Spreadsheet formula use  Articles
2 replies
rizvisa1
Apr 19, 2010 at 10:27 AM
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Apr 19, 2010 at 10:27 AM
Sorry even though you did excellent work in explaining but I am a thick headed. Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
ok, i got PART 2 worked out, and I know a workaround way of getting it automatically inserted into the other worksheet.
NOW, if someone could help me simplify this so that I can duplicate it easily for every "category", that would be wonderful!!! :)
=SUM(IF($D$5 = "Materials", $E$5, 0),IF($D$6 = "Materials", $E$6, 0),IF($D$7 = "Materials", $E$7, 0),IF($D$8 = "Materials", $E$8, 0),IF($D$9 = "Materials", $E$9, 0),IF($D$10 = "Materials", $E$10, 0),IF($D$11 = "Materials", $E$11, 0),IF($D$12 = "Materials", $E$12, 0),IF($D$13 = "Materials", $E$13, 0),IF($D$14 = "Materials", $E$14, 0),IF($D$15 = "Materials", $E$15, 0),IF($D$16 = "Materials", $E$16, 0),IF($D$17 = "Materials", $E$17, 0),IF($D$18 = "Materials", $E$18, 0),IF($D$19 = "Materials", $E$19, 0),IF($D$20 = "Materials", $E$20, 0),IF($D$21 = "Materials", $E$21, 0),IF($D$22 = "Materials", $E$22, 0),IF($D$23 = "Materials", $E$23, 0),IF($D$24 = "Materials", $E$24, 0),IF($D$25 = "Materials", $E$25, 0),IF($D$26 = "Materials", $E$26, 0),IF($D$27 = "Materials", $E$27, 0),IF($D$28 = "Materials", $E$28, 0),IF($D$29 = "Materials", $E$29, 0),IF($D$30 = "Materials", $E$30, 0),IF($D$31 = "Materials", $E$31, 0),IF($D$32 = "Materials", $E$32, 0),IF($D$33 = "Materials", $E$33, 0),IF($D$34 = "Materials", $E$34, 0),IF($D$35 = "Materials", $E$35, 0),IF($D$36 = "Materials", $E$36, 0),IF($D$37 = "Materials", $E$37, 0),IF($D$38 = "Materials", $E$38, 0),IF($D$39 = "Materials", $E$39, 0),IF($D$40 = "Materials", $E$40, 0),IF($D$41 = "Materials", $E$41, 0),IF($D$42 = "Materials", $E$42, 0),IF($D$43 = "Materials", $E$43, 0),IF($D$44 = "Materials", $E$44, 0),IF($D$45 = "Materials", $E$45, 0),IF($D$46 = "Materials", $E$46, 0),IF($D$47 = "Materials", $E$47, 0),IF($D$48 = "Materials", $E$48, 0),IF($D$49 = "Materials", $E$49, 0),IF($D$50 = "Materials", $E$50, 0),IF($D$51 = "Materials", $E$51, 0),IF($D$52 = "Materials", $E$52, 0),IF($D$53 = "Materials", $E$53, 0),IF($D$54 = "Materials", $E$54, 0), etc, etc))
Perhaps like this instead to shorten the formula (although this one doesn't work)?????
=SUM(IF($D$5:$D$282="Materials",$E$5:$E$282,0))
or
=SUM(IF($D$5$D$282="Materials",$E$5$E$282,0))
to say:
=SUM(IF($D$5 through $D$282="Materials",$E$5 through $E$282,0))
********************************************
PLUS, I'M STILL HOPING FOR ANSWERS TO PART 1...
********************************************
NOW, if someone could help me simplify this so that I can duplicate it easily for every "category", that would be wonderful!!! :)
=SUM(IF($D$5 = "Materials", $E$5, 0),IF($D$6 = "Materials", $E$6, 0),IF($D$7 = "Materials", $E$7, 0),IF($D$8 = "Materials", $E$8, 0),IF($D$9 = "Materials", $E$9, 0),IF($D$10 = "Materials", $E$10, 0),IF($D$11 = "Materials", $E$11, 0),IF($D$12 = "Materials", $E$12, 0),IF($D$13 = "Materials", $E$13, 0),IF($D$14 = "Materials", $E$14, 0),IF($D$15 = "Materials", $E$15, 0),IF($D$16 = "Materials", $E$16, 0),IF($D$17 = "Materials", $E$17, 0),IF($D$18 = "Materials", $E$18, 0),IF($D$19 = "Materials", $E$19, 0),IF($D$20 = "Materials", $E$20, 0),IF($D$21 = "Materials", $E$21, 0),IF($D$22 = "Materials", $E$22, 0),IF($D$23 = "Materials", $E$23, 0),IF($D$24 = "Materials", $E$24, 0),IF($D$25 = "Materials", $E$25, 0),IF($D$26 = "Materials", $E$26, 0),IF($D$27 = "Materials", $E$27, 0),IF($D$28 = "Materials", $E$28, 0),IF($D$29 = "Materials", $E$29, 0),IF($D$30 = "Materials", $E$30, 0),IF($D$31 = "Materials", $E$31, 0),IF($D$32 = "Materials", $E$32, 0),IF($D$33 = "Materials", $E$33, 0),IF($D$34 = "Materials", $E$34, 0),IF($D$35 = "Materials", $E$35, 0),IF($D$36 = "Materials", $E$36, 0),IF($D$37 = "Materials", $E$37, 0),IF($D$38 = "Materials", $E$38, 0),IF($D$39 = "Materials", $E$39, 0),IF($D$40 = "Materials", $E$40, 0),IF($D$41 = "Materials", $E$41, 0),IF($D$42 = "Materials", $E$42, 0),IF($D$43 = "Materials", $E$43, 0),IF($D$44 = "Materials", $E$44, 0),IF($D$45 = "Materials", $E$45, 0),IF($D$46 = "Materials", $E$46, 0),IF($D$47 = "Materials", $E$47, 0),IF($D$48 = "Materials", $E$48, 0),IF($D$49 = "Materials", $E$49, 0),IF($D$50 = "Materials", $E$50, 0),IF($D$51 = "Materials", $E$51, 0),IF($D$52 = "Materials", $E$52, 0),IF($D$53 = "Materials", $E$53, 0),IF($D$54 = "Materials", $E$54, 0), etc, etc))
Perhaps like this instead to shorten the formula (although this one doesn't work)?????
=SUM(IF($D$5:$D$282="Materials",$E$5:$E$282,0))
or
=SUM(IF($D$5$D$282="Materials",$E$5$E$282,0))
to say:
=SUM(IF($D$5 through $D$282="Materials",$E$5 through $E$282,0))
********************************************
PLUS, I'M STILL HOPING FOR ANSWERS TO PART 1...
********************************************
rizvisa1
Apr 19, 2010 at 11:46 AM
 Posts
 4479
 Registration date
 Thursday January 28, 2010
 Status
 Contributor
 Last seen
 May 5, 2022
Apr 19, 2010 at 11:46 AM
Actually you are on right track. You need to use SUMIF formula, some thing like this
On sheet2 you can have this formula where the total appear
=SUMIF(Sheet1!D:D,J2,Sheet1!E:E)
here J2 refers to cell where the value would be like materials, supplies etc
or
=SUMIF(Sheet1!D:D,"Materials",Sheet1!E:E)
Once again as I said before if you put up a file, it would easy to see what you seek
On sheet2 you can have this formula where the total appear
=SUMIF(Sheet1!D:D,J2,Sheet1!E:E)
here J2 refers to cell where the value would be like materials, supplies etc
or
=SUMIF(Sheet1!D:D,"Materials",Sheet1!E:E)
Once again as I said before if you put up a file, it would easy to see what you seek