Excel Sumif ??

[Javi]

Hi All,

Can someone please help me change the below formula so that any matches in column $A$1:$A$1000 sums the matches in Column $D$1:$D$1000.

=SUMIF($A$1:$A$1000,"CL2579",$D$1:$D$1000)

 

[Jonmo1]

You need to create a unique list of each item.
See example below.

Unique list in D2:D6
Formulas in E2:E6 indivually sumif each unique item.
Then E8 is a Sum of those individual sumifs
Then E10 is a formula that will sum all of them in one formula (skipping the individual formulas)

Hope this helps.

Excel Workbook
	A	B	C	D	E
1	A	B		Unique List
2	123	$2.00		123	5.5
3	222	$3.50		222	3.5
4	321	$1.50		321	6.5
5	421	$2.25		421	2.25
6	123	$1.00		555	5
7	123	$2.50
8	555	$5.00		Total	22.75
9	321	$2.00
10	321	$3.00		All in One	22.75
Sheet1

 

[T. Valko]

You need to create a unique list of each item.

And that's really easy to do:

http://contextures.com/xladvfilter01.html#FilterUR

 

[Javi]

Thanks everyone, The below from T. Valko worked Great!! Thanks again.

=SUMIF(A$1:A$9,A1,B$1:B$9)

 

[T. Valko]

Thanks everyone, The below from T. Valko worked Great!! Thanks again.

You're welcome. Thanks for the feedback!

 

[Javi]

If I wanted the average of the matching sum and not the sum how would I approach this? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This formula gives me the total in column C would like the Avg of the total. <o></o>

<o></o>

=SUMIF(A$1:A$9,A1,B$1:B$9)<o></o>

<o></o>
<o> </o>
-----A---------B-----------C
1--123-----$ 2.00------$ 1.83
2--222-----$ 3.50------$ 3.50
3--321-----$ 1.50------$ 2.16
4--421-----$ 2.25------$ 2.25
5--123-----$ 1.00------$ 1.83
6--123-----$ 2.50------$ 1.83
7--555-----$ 5.00------$ 5.00
8--321-----$ 2.00------$ 2.16
9--321-----$ 3.00------$ 2.16<o></o>
<o> </o>
Thanks for all the help!!!<o></o>

 

[T. Valko]

If I wanted the average of the matching sum and not the sum how would I approach this? <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This formula gives me the total in column C would like the Avg of the total. <o></o>
<o></o>
<o></o>
-----A---------B-----------C
1--123-----$ 2.00------$ 1.83
2--222-----$ 3.50------$ 3.50
3--321-----$ 1.50------$ 2.16
4--421-----$ 2.25------$ 2.25
5--123-----$ 1.00------$ 1.83
6--123-----$ 2.50------$ 1.83
7--555-----$ 5.00------$ 5.00
8--321-----$ 2.00------$ 2.16
9--321-----$ 3.00------$ 2.16<o></o>
<o></o>
Thanks for all the help!!!<o></o>

Try one of these...

If you're using Excel 2007 or later...

=AVERAGEIF(A$1:A$9,A1,B$1:B$9)

Copy down as needed.

This array formula** will work in ALL versions of Excel:

=AVERAGE(IF(A$1:A$9=A1,B$1:B$9))

Copy down as needed.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Javi]

Worked like a charm!!!

Excel 2007

=AVERAGEIF(A$1:A$9,A1,B$1:B$9)

Thank you!

How does Jonmo1 insert the images when post?

 

[T. Valko]

Worked like a charm!!!
Thank you!

You're welcome!

How does Jonmo1 insert the images when post?

This add-in:

http://www.excel-jeanie-html.de/index.php?f=1

 

[Javi]

Thanks! Works well.

[RANGE=cls:xl2bb-100][XR][XH=cs:5]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][/XR][XR][XH]1[/XH][XD][/XD][XD=h:c]Test[/XD][XD=h:c]Test[/XD][XD=h:c|c:ff0000]Test[/XD][/XR][XR][XH]2[/XH][XD][/XD][XD=h:c|bc:c0c0c0]2[/XD][XD=h:c|bc:ffff00]3[/XD][XD=h:c]4[/XD][/XR][XR][XH]3[/XH][XD][/XD][XD=h:c]5[/XD][XD=h:c]3[/XD][XD=h:c|bc:ccccff]5[/XD][/XR][XR][XH]4[/XH][XD=h:r]Total[/XD][XD=h:c|fw:b|cls:fx][FORMULA==SUM(B2:B3)]7[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==SUM(C2:C3)]6[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==SUM(D2:D3)]9[/FORMULA][/XD][/XR][XR][XH=cs:5][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

 

[Javi]

[RANGE=cls:xl2bb-100][XR][XH=cs:5]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][/XR][XR][XH]1[/XH][XD][/XD][XD=h:c]Test[/XD][XD=h:c]Test[/XD][XD=h:c|c:ff0000]Test[/XD][/XR][XR][XH]2[/XH][XD][/XD][XD=h:c|bc:c0c0c0]2[/XD][XD=h:c|bc:ffff00]3[/XD][XD=h:c]4[/XD][/XR][XR][XH]3[/XH][XD][/XD][XD=h:c]5[/XD][XD=h:c]3[/XD][XD=h:c|bc:ccccff]5[/XD][/XR][XR][XH]4[/XH][XD=h:r]Total[/XD][XD=h:c|fw:b|cls:fx][FORMULA==SUM(B2:B3)]7[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==SUM(C2:C3)]6[/FORMULA][/XD][XD=h:c|fw:b|cls:fx][FORMULA==SUM(D2:D3)]9[/FORMULA][/XD][/XR][XR][XH=cs:5][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]