Need formulas for accounting spreadsheets

Closed
ikanikon - Apr 19, 2010 at 09:20 AM
rizvisa1
Posts
4479
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)
}

2 replies

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