SUM(IF( FORMULA ?

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Aug 31, 2005
Messages
5,828
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
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,810
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
THANK YOU.
VERY MUCH...
 

Forum statistics

Threads
1,136,633
Messages
5,676,896
Members
419,657
Latest member
ExcelAl1

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
Top