sumif question

[KarenC]

I have a table of information already set up and I am trying the sumif formula and it always returns a value of 0. If I retype all the information on another spreadsheet, it works...but I don't want to do that since I already have it all listed already. Would someone be willing to look at my excel file and tell me what the heck is wrong?

[kkknie]

What are you SUMIFfing? In many cases, the problem has something to do with text. Either numbers defined as text or extra space in the text.

Check your actual data for these conditions. If that's not the case, post your formula and I'll take a gander.

K

[KarenC]

Well, there is an account number in column say... A1:a6 and there are values in column d1:d6 (however, these values are the result of an "if" statement)...
A D
1 60100 20
2 60200 30
3 60400 40
4 60100 10
5 60225 5
6 60100 40


so I am doing =sumif(a1:a6,60100,d1:d6) this should = 70

might be better if I sent a spreadsheet...[/u]

[Aladin Akyurek]

Well, there is an account number in column say... A1:a6 and there are values in column d1:d6 (however, these values are the result of an "if" statement)...
A D
1 60100 20
2 60200 30
3 60400 40
4 60100 10
5 60225 5
6 60100 40


so I am doing =sumif(a1:a6,60100,d1:d6) this should = 70

might be better if I sent a spreadsheet...[/u]

What are the results of the following formulas:

=ISNUMBER(A1)

and

=ISNUMBER(B1) ?

[Juan Pablo González]

Try with

=SUMIF(A1:A6,"60100",D1:D6)

to see if the problem you're having is that A1:A6 are texts, not numbers.

[KarenC]

This is the formula that is in the D column...

=IF(ISNA(VLOOKUP($K404&M$50,accountonlydload,5,0)),0,(VLOOKUP($K404&M$50,accountonlydload,5,FALSE)))


and the value returned is 35,574, however when I do my sumif, it returns a 0, if I just type in the cell the 35,574, it will return the correct value.

[KarenC]

Did I stump ya?

[Yogi Anand]

Hi Karen:

I would like you to try the following formulation ...

=SUM(IF(--(A1:A6)=60100,--(D1:D6)))

this is an array formula and must be entered with CTRL+SHIFT+ENTER not just with ENTER

Please let me know if this works for you -- and then let us take it from there!

[Zack Barresse]

hello KarenC,

as you can see, there are many ways to go about any one item. some examples, as variations have been shown, can all result in the same answer. if your numbers in A are formatted as text, there are a few ways to deal with that...

******** ******************** ************************************************************************>

Microsoft Excel - Book1.xls ___Running: xl2002 XP : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
C1C2C3 =
	A	B	C	D
1	60100	20	70	*
2	60200	30	70	*
3	60400	40	70	*
4	60100	10	*	*
5	60225	5	*	*
6	60100	40	*	*
7	/\*formatted*as*text	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

=SUMIF(A1:A6,"60100",B1:B6)

=SUMIF(A1:A6,60100,B1:B6)

=SUMPRODUCT(--(--(A1:A6)=60100),B1:B6)

all are confirmed with only enter.

hth

[Yogi Anand]

Hi Karen:

If you are comparing with an entry in a cell, say E1, then try this extended version of my earlier post ...

******** ******************** ************************************************************************>

Microsoft Excel - y040510h1.xls ___Running: xl97 : OS = Windows XP
File Edit View Insert Options Tools Data Window Help About
F1 =
	A	B	C	D	E	F
1	60100	*	*	20	60100	70
2	60200	*	*	30	*	*
3	60400	*	*	40	*	*
4	60100	*	*	10	*	*
5	60225	*	*	5	*	*
6	60100	*	*	40	*	*
Sheet11 *

[HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

formula in cell F1 is ...

=SUM(IF(--(A1:A6)=E1+0,--(D1:D6)))

this is an array formula and must be entered with CTRL+SHIFT+ENTER rather than with just ENTER.

And do let me know if it works for you -- and then let us take it from there.