# sumif question

This is a discussion on sumif question within the Excel Questions forums, part of the Question Forums category; I have a table of information already set up and I am trying the sumif formula and it always returns ...

1. ## sumif question

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?

2. 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

3. ## Re: sumif question

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]

4. ## Re: sumif question

Originally Posted by 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]
What are the results of the following formulas:

=ISNUMBER(A1)

and

=ISNUMBER(B1) ?

5. ## Re: sumif question

Try with

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

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

6. ## Re: sumif question

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

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.

7. ## Did I stump ya?

Did I stump ya?

8. ## Re: sumif question

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!

9. 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
601002070*
2
602003070*
3
604004070*
4
6010010**
5
602255**
6
6010040**
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

10. ## Re: sumif question

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**206010070
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.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•