Formula for Coding Charges

[helopilot]

I cant seem to figure out a code to give me what I'm looking for. I want it to look at Colum F for the code, take the amount in Column E and any others with the same code and put the Total Amount in Column H next to the corresponding code. So in this example H1 = $4326.00 H2 = $256.32 and so on. Hope that is clear.

Excel 2010
	A	B	C	D	E	F	G	H
1	Type	Trans Date	Post Date	Description	Amount		Types of Charge	Total Amount
2	Sale	4/13	4/14	Charge 1	$3,396.00	a	a
3	Sale	4/13	4/14	Charge 2	$255.33	b	b
4	Sale	4/13	4/14	Charge 3	$63.51	c	c
5	Sale	4/13	4/15	Charge 4	$930.00	a
6	Sale	4/12	4/12	Charge 5	$0.99	b
7	Sale	4/12	4/13	Charge 6	$70.36	c
Activity

 

[jeffreybrown]

Try in H2 copied down

=SUMIF($F$2:$F$7,G2,$E$2:$E$7)

 

[helopilot]

Try in H2 copied down

=SUMIF($F$2:$F$7,G2,$E$2:$E$7)

That's it!! Thanks Jeffrey!!

 

[jeffreybrown]

Great news. Thanks for the feedback and you're welcome. Happy to help.

 

[helopilot]

Is there a way to use just the entire F Column instead of a range? I never know exactly what the range is going to be and would like to set this up as a template so to speak.

 

[jeffreybrown]

Sure

=SUMIF(F:F,G2,E:E)

 

[helopilot]

Ah yeah I tried that but my syntax was a little off as I see now. Thanks again Jeffrey.

 

[helopilot]

Ok now I need help with Conditional Formatting, I need it to look at Column F and depending on which letter code is present Highlight the corresponding cells in that row to a certain color. IE if F2 is a then Highlight A2:E2 Red.

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Type	Trans Date	Post Date	Description	Amount	Code		Code	Total Amount
2	Sale	4/13	4/14	Chagre 1	$3,396.00	a		a	-3396
3	Sale	4/13	4/14	Chagre 2	$255.33	b		b	-255.33
4	Sale	4/13	4/14	Chagre 3	$63.51	c		c	-63.51
5	Sale	4/13	4/15	Chagre 4	$930.00	d		d	-930
6	Sale	4/12	4/12	Chagre 5	$0.99	e		e	-0.99
7	Sale	4/12	4/13	Chagre 6	$70.36	f		f	-70.36
8	Sale	4/11	4/12	Chagre 7	$330.49	g		g	-330.49
Sheet1

Cell Formulas
Range	Formula
I2	=SUMIF(F:F,H2,E:E)

 

[jeffreybrown]

First question, how far down the alphabet does this go?

You have down to "g" right now which if you wanted seven different colors would be seven different conditions in CF.

Anyway, for the "a"

Conditional Formatting

• Highlight applicable range >> =$A$2:$E$8
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =$F2="a"
• Format… [Number, Font, Border, Fill]
• OK >> OK

 

[helopilot]

First question, how far down the alphabet does this go?

You have down to "g" right now which if you wanted seven different colors would be seven different conditions in CF.

Anyway, for the "a"

Conditional Formatting

• Highlight applicable range >> =$A$2:$E$8
• Home Tab >> Styles >> Conditional Formatting
• New Rule >> Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =$F2="a"
• Format… [Number, Font, Border, Fill]
• OK >> OK

Not sure just yet how many codes there will be. I think this will work out for now. Thanks again Jeffrey!!