# Excel Sumif ??

#### Javi

##### Active Member
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)

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
In what way does that not work?

The formula as you have it will do what you ask..

It will sum cells in D1:D1000 where cells in A1:D1000 = "CL2579"

Excel Workbook
1Random Text116
2Random Text2
3CL25793
4Random Text4
5CL25795
6Random Text6
7Random Text7
8CL25798
9Random Text9
10Random Text10
Sheet1

Sorry I was not clear.

I can not insert the "CL2579" there are to many I need the formula to check all matches in coloumn "A" and sum column "D".

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

Maybe this is a sumproduct task?

Maybe this is a sumproduct task?
Can you post some data and tell us what result you expect?

Sorry I was not clear.

I can not insert the "CL2579" there are to many I need the formula to check all matches in coloumn "A" and sum column "D".

Sorry, still not clear.

All matches "to what" in column "A" ??

do you have another list of matches somewhere, say B1:B10

In C1, put =SUMIF(\$A\$1:\$A\$1000,B1,\$D\$1:\$D\$1000)
and fill down to C10

Try:

Code:
``=SUMPRODUCT(SUMIF(A\$1:A\$1000,CriteriaList,D\$1:D\$1000))``

Where CriteriaList is a range of cells containing values such as CL2579.

Matty

This may help "Sorry guys"

-----A---------B-----------C
1--123-----\$ 2.00------\$ 5.50
2--222-----\$ 3.50------\$ 3.50
3--321-----\$ 1.50------\$ 6.50
4--421-----\$ 2.25------\$ 2.25
5--123-----\$ 1.00------\$ 5.50
6--123-----\$ 2.50------\$ 5.50
7--555-----\$ 5.00------\$ 5.00
8--321-----\$ 2.00------\$ 6.50
9--321-----\$ 3.00------\$ 6.50

"The formula will be in column C"
Part number in column "A"
Price in column "B"

I need a total price for all of the same parts in column "A".

Line one column C= \$5.50 (\$2.00 from line1, \$1.00 from line5, and \$2.50 from line6)

Line two column C= \$3.50 (no other matches)

Line three column C = \$6.50 (has 3 matches lines 3, 8 and 9)

This may help "Sorry guys"

-----A---------B-----------C
1--123-----\$ 2.00------\$ 5.50
2--222-----\$ 3.50------\$ 3.50
3--321-----\$ 1.50------\$ 6.50
4--421-----\$ 2.25------\$ 2.25
5--123-----\$ 1.00------\$ 5.50
6--123-----\$ 2.50------\$ 5.50
7--555-----\$ 5.00------\$ 5.00
8--321-----\$ 2.00------\$ 6.50
9--321-----\$ 3.00------\$ 6.50

"The formula will be in column C"
Part number in column "A"
Price in column "B"

I need a total price for all of the same parts in column "A".

Line one column C= \$5.50 (\$2.00 from line1, \$1.00 from line5, and \$2.50 from line6)

Line two column C= \$3.50 (no other matches)

Line three column C = \$6.50 (has 3 matches lines 3, 8 and 9)

Enter this formula in C1 and copy down:

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

Replies
9
Views
541
Replies
18
Views
492
Replies
2
Views
131
Replies
1
Views
146
Replies
4
Views
111

1,203,456
Messages
6,055,543
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back