Need formulas for accounting spreadsheets
Closed
ikanikon

Apr 19, 2010 at 09:20 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022  Apr 19, 2010 at 11:46 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022  Apr 19, 2010 at 11:46 AM
Related:
 Need formulas for accounting spreadsheets
 Microsoft office accounting  Download  Billing and accounting
 Spreadsheet formulas  Guide
 Little alchemy formulas  Guide
 Accounting software free download  Download  Billing and accounting
 Excel formula for number to words  Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 19, 2010 at 10:27 AM
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Apr 19, 2010 at 11:46 AM
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