# SUM(IF( FORMULA ?

#### srizki

##### Well-known Member
I am trying to do conditional sum. I have two tables both have ID numbers like E5G79296, E6G85377, etc. Table A may be missing some ID’s, so does table B, I want to sum the corresponding values in only if it the ID’ are same, I can not use SUMPRODUCT because one column has almost 5000 rows and another less that 2000. Can I use arrary SUM(IF formula if the tables are on two different sheets.?
Thanks

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### NBVC

##### Well-known Member
Possibly, but can you give a scaled down example of what you are looking at and what you want, so we can determine the possibility better?

Use Colo's Html Maker (instructions in my signature).

#### srizki

##### Well-known Member
ABCD
3CAPIDTotalNUMBERTotal
4E4G76147-W3B69685668.71
5E5B79483-E3B863226,537.62
6E6B85687-W3B698027,912.78
7W6B85692-W3G6968814,417.30
8W6B85719-W3G6979317,555.67
9W5B85009(0.00)W3G6974417,666.05
10E6G85516(0.00)W3G6968723,169.81
11E6G8552138.00W4G7390130,407.06
12W5B78660(150.00)W3G6974836,452.10
13E6B86166186.16W3G6985150,457.35
14E8B56169321.40W3G6954871,310.29
15E5B79730363.31W4G7386188,652.22
16E6B86314813.95W3G69702148,604.75
17E6B85504874.00W3G69690314,529.53
18E6B85447992.80W3G69747469,434.69
19W5G78641(1,000.00)E5G790401,233,000.00
20E5B85293(1,340.35)E6G855248,556.00
21E6B863181,365.76E6B857192,500.00
22E3G735841,462.50E6B8631920,038.42
23E6B862501,561.56E6G8532554,253.60
24E6B86168(1,820.77)E6G85518162,163.85
June GL & FA workings

This is just a small piece, so the ID may not be same, in column A and C.
thanks

#### NBVC

##### Well-known Member
So what is it exactly you want to happen? If the same code appears in the same Row of Column A and Column C, then sum Column D? Or the code in A appears anywhere in C, then sum the corresponding D?

Also, how large is each range?

#### srizki

##### Well-known Member
Code appear anywhere in the cloumn, I want total of all the values that matches their ID. I am trying basic SUMIF formula but it is not working,
=SUMIF(\$A\$4:\$A\$95,\$C\$4:\$C\$73,\$B\$4:\$B\$95), what am I doing wrong here?
Thanks

#### NBVC

##### Well-known Member
This will sum column B as long as any values in column C are found in Column A. Did I get you right?

=SUM(IF(ISNUMBER(SEARCH(\$C\$4:\$C\$73,\$A\$4:\$A\$95)),\$B\$4:\$B\$73))

confirmed with CTRL+SHIFT+ENTER not just ENTER

#### srizki

##### Well-known Member
Thank you very much, I havent tried the formula yet, but one more question, is there a difference in ISNUMBER and ISNA.?

#### NBVC

##### Well-known Member
Thank you very much, I havent tried the formula yet, but one more question, is there a difference in ISNUMBER and ISNA.?

ISNUMBER() returns TRUE/FALSE depending on whether the result of a formula or the value in a cell is a number.

ISNA() returns TRU/FALSE depending on whether the result of a formula is or a value in a cell is "not available" (i.e. result not found) ---it is an error message really.

See Excel help for more.

THANK YOU.
VERY MUCH...

You are welcome!

Replies
0
Views
133
Replies
0
Views
266
Replies
3
Views
394
Replies
1
Views
1K
Replies
3
Views
128

1,171,203
Messages
5,874,330
Members
433,044
Latest member
drewbizzy

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