# Formula Help

#### srizki

##### Well-known Member
Formula Help
I have ID’s in column A and corresponding values in column B, and againg ID’s in Column C and corresponding values in column D. So I have two tables on one sheet.I want to add all the values in column B that have same ID’s in Column A and C. same thing I will do to values in D, that is to add up all the values that have ID in both tables, column A and column C.
Thanks

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
To sum column B:

=SUMPRODUCT(--(A1:A100=C1:C100),B1:B100)

To sum column D:

=SUMPRODUCT(--(A1:A100=C1:C100),D1:D100)

Thanks Oaktree, I have been trying this formula but I was not getting answer, I don't know what I was doing wrong, I also tried, array,
=SUM(IF(A4:A95=C4:C73,D4:D73)) and sumif etc, nothing worked.
Thanks.

I had this solution, but it is a little bit less neat.

=IF(ISNA(MATCH(A1,C:C,0)),0,B1)
in column E, and drag down

=SUM(E:E)
somewhere else. this returns the total

I am getting #N/A error, I am using
=SUMPRODUCT(--(A5:A575=D5:D455),B5:B575)
and
=SUMPRODUCT(--(A5:A575=D5:D455),E7:E455)

these two tables are pivot and rows are different in numbers as you can see in my formula.

I am not very good with sumproduct, but have you shift-entered the formula?

Your arrays have to be the same size.

=SUMPRODUCT(--(A5:A575=D5:D455),B5:B575)
should be
=SUMPRODUCT(--(A5:A575=D5:D575),B5:B575)

yes Oaktree, it worked,
thanks

Replies
5
Views
139
Replies
8
Views
140
Replies
5
Views
210
Replies
1
Views
89
Replies
3
Views
95

1,218,516
Messages
6,142,921
Members
450,455
Latest member
Handienak

### 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