Can I get a Sum From All Values in a column that have a specific value in the column next to them?

[Gryrok]

So, I'd like to have a list of transactions, and have each transaction be labeled something. Next to this list I'd like to have a total value of each transaction with a specific label. Like, if we were totally an amount spend on an event, we could say "$5" "Promotions" and "$10" "Services" etc. etc., and in the table next to that, it would, in a single cell, add the total value assigned each label, like it shows below.

What would I put in the cell shown below to get the total value of everything labeled A?


Thanks in advance for your help, and I apologize if this has been asked/answered, I tried searching for it, but I just don't know how to look or what to call it.

date	item	value		Total Values Based on Item
1-Sep	A	1		A	HOW DO I GET THIS CELL?
2-Sep	C	2		B
3-Sep	D	1		C
4-Sep	A	2		D
1-Sep	B	3		E
2-Sep	E	4
3-Sep	C	3
4-Sep	D	1
1-Sep	C	2
2-Sep	A	2
3-Sep	C	1
4-Sep	A	2

<tbody>
</tbody>

 

[Weazel]

something like...

F2=SUMIF($B$2:$B$13,E2,$C$2)

Row\Col	A​	B​	C​	D​	E​	F​
1​	date	item	value		Items	Total
2​	1-Sep​	A	1​		A	7​
3​	2-Sep​	C	2​		B	3​
4​	3-Sep​	D	1​		C	8​
5​	4-Sep​	A	2​		D	2​
6​	1-Sep​	B	3​		E	4​
7​	2-Sep​	E	4​
8​	3-Sep​	C	3​
9​	4-Sep​	D	1​
10​	1-Sep​	C	2​
11​	2-Sep​	A	2​
12​	3-Sep​	C	1​
13​	4-Sep​	A	2​

<tbody>
</tbody>

 

[oldbrewer]

date

item

value

Total Values Based on Item

01-Sep

A

1

A

7

02-Sep

C

2

B

3

03-Sep

D

1

C

8

04-Sep

A

2

D

2

01-Sep

B

3

E

4

02-Sep

E

4

03-Sep

C

3

04-Sep

D

1

01-Sep

C

2

02-Sep

A

2

the 7 next to a comes from

03-Sep

C

1

04-Sep

A

2

=SUMPRODUCT(($B$2:$B$13=E2)*($C$2:$C$13))

<colgroup><col span="13"></colgroup><tbody>
</tbody>

 

[oldbrewer]

date

item

value

01-Sep

02-Sep

03-Sep

04-Sep

01-Sep

A

1

A

3

4

7

9

02-Sep

C

2

B

3

0

0

5

03-Sep

D

1

C

8

8

7

2

04-Sep

A

2

D

0

2

2

3

01-Sep

B

3

E

5

4

0

0

02-Sep

E

4

72

03-Sep

C

3

04-Sep

D

1

01-Sep

C

2

02-Sep

A

2

the 7 next to a comes from

03-Sep

C

1

04-Sep

A

2

=SUMPRODUCT(($B$2:$B$13=E2)*($C$2:$C$13))

01-Sep

C

2

02-Sep

D

1

03-Sep

A

2

then I added an extra term to allow you to analyse by date

04-Sep

B

3

01-Sep

E

4

02-Sep

C

3

so the 3 under 01-Sep comes from

03-Sep

D

1

04-Sep

C

2

01-Sep

A

2

=SUMPRODUCT(($B$2:$B$37=$E2)*($C$2:$C$37)*($A$2:$A$37=F$1))

02-Sep

C

1

03-Sep

A

1

04-Sep

D

2

01-Sep

C

1

02-Sep

A

2

03-Sep

C

3

04-Sep

A

4

01-Sep

C

3

02-Sep

D

1

03-Sep

A

2

04-Sep

B

2

01-Sep

E

1

02-Sep

C

2

03-Sep

A

2

04-Sep

A

1

72

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[hiker95]

Gryrok,

How about something like this?

Excel 2007
	A	B	C	D	E
1	date	item	value	Item	Total Values Based on Item
2	1-Sep	A	1	A	7
3	2-Sep	C	2	B	3
4	3-Sep	D	1	C	8
5	4-Sep	A	2	D	2
6	1-Sep	B	3	E	4
7	2-Sep	E	4
8	3-Sep	C	3
9	4-Sep	D	1
10	1-Sep	C	2
11	2-Sep	A	2
12	3-Sep	C	1
13	4-Sep	A	2
14
Sheet1

Cell Formulas
Range	Formula
E2	=SUMIF($B$2:$B$13,D2,$C$2:$C$13)

The formula in cell E2, copied down:

=SUMIF($B$2:$B$13,D2,$C$2:$C$13)

 

[Weazel]

post 2 would work just fine

 

[hiker95]

if we were totally an amount spend on an event, we could say "$5" "Promotions" and "$10" "Services" etc. etc., and in the table next to that, it would, in a single cell, add the total value assigned each label, like it shows below.

It looks like we all have missed the above.

Gryrok,

Can we see what other $ values, and, text, that go with the other etc.'s?

 

[oldbrewer]

the OP needs to clarify - his post 1 is badly written and has typos - OP ?

 

[hiker95]

Gryrok,

See reply #2 at the next link, to show an actual screenshot of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729

 

[Gryrok]

Oh my goodness! Thank you all so very much for a nice simple clean solution. I figured there had to be a function somewhere, and I just didn't know which it was, and this was IT EXACTLY! Thanks again!