SUM(IF( FORMULA ?

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,831
Office Version
  1. 365
Platform
  1. Windows
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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).
 
Upvote 0
FA_Additions_YTD 2006.xls
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thank you very much, I havent tried the formula yet, but one more question, is there a difference in ISNUMBER and ISNA.?
Thanks againg for your help.
 
Upvote 0
Thank you very much, I havent tried the formula yet, but one more question, is there a difference in ISNUMBER and ISNA.?
Thanks againg for your help.

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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top