SUM(IF( FORMULA ?

[srizki]

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

 

[NBVC]

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]

FA_Additions_YTD 2006.xls
	A	B	C	D
3	CAPID	Total	NUMBER	Total
4	E4G76147	-	W3B69685	668.71
5	E5B79483	-	E3B86322	6,537.62
6	E6B85687	-	W3B69802	7,912.78
7	W6B85692	-	W3G69688	14,417.30
8	W6B85719	-	W3G69793	17,555.67
9	W5B85009	(0.00)	W3G69744	17,666.05
10	E6G85516	(0.00)	W3G69687	23,169.81
11	E6G85521	38.00	W4G73901	30,407.06
12	W5B78660	(150.00)	W3G69748	36,452.10
13	E6B86166	186.16	W3G69851	50,457.35
14	E8B56169	321.40	W3G69548	71,310.29
15	E5B79730	363.31	W4G73861	88,652.22
16	E6B86314	813.95	W3G69702	148,604.75
17	E6B85504	874.00	W3G69690	314,529.53
18	E6B85447	992.80	W3G69747	469,434.69
19	W5G78641	(1,000.00)	E5G79040	1,233,000.00
20	E5B85293	(1,340.35)	E6G85524	8,556.00
21	E6B86318	1,365.76	E6B85719	2,500.00
22	E3G73584	1,462.50	E6B86319	20,038.42
23	E6B86250	1,561.56	E6G85325	54,253.60
24	E6B86168	(1,820.77)	E6G85518	162,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]

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]

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]

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]

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]

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]

THANK YOU.
VERY MUCH...

 

[NBVC]

You are welcome!