# 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
I have a few questions for a spreadsheets program I am creating...
Explaining what it does, specifically in reference to my questions:

Worksheet A
COLUMN 1C COLUMN 2D \$ COLUMN 3E
Check Written To (John), Categories (Supplies), \$100.00
Check Written To (Sam), Categories (Materials), \$100.00
Check Written To (Joe), Categories (Supplies), \$20.00

Worksheet B
COLUMN 1J \$ COLUMN 2K
Category Added Amounts from \$ above
Supplies \$120.00
Materials \$100.00

What I need to figure out are the formulas for these questions:

Referencing Worksheet A only
1) Can I make a formula that automatically places the category
(based on the information typed into Column 1C) into COLUMN 2D?

{
I'm limited in my formula skills, but I want something like this, knowing it is ridiculously wrong and it's probably not even an IF THEN statement...
IF(my variable, is true, not true)
=IF(C5=Wcompany, D5=Materials, no idea for false part
C5=Pcompany D5=Towing, false part
C5=Ycompany D5=Parts, false part
etc, etc)
}

Referencing BOTH worksheets
2) Can I make a formula that automatically adds the (example: "Supplies") and inserts the totals from Worksheet A column 3E into Worksheet B Column 2K (like I did above in the sample)?

formula in cell below column 2K -
{
=SUM(Total of "Materials" from Worksheet A Column 3E)
}
Related:

## 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
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...
********************************************
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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