1. ## Sumif Problem

I have a spreasheet containing account numbers in Sheet1 and account numbers and values on sheet "imported data"

I have tried to use the SUMIFformula, but it returns zero. The reason for using the sumif formula is that I the same account number contains different values as it is loaded in the Trial Balance with different descriptions

The reason where it may return zero is the the account numbers appears in Sheet 1 are numbers and those on sheet "imported data" are text

I would like a formula on sheet Col C to add the values on sheet "imported data" based on the account number on sheet1

Your assistance in resolving this is most appreciated

2. Try filling this down in Sheet1

=IF(A1="",0,SUMIF('Imported Data'!\$A1:\$A25,C1,'Imported Data'!E1:E25))

3. Hi Kevin

Thanks for the help. Can't believe that I messed this formula up. I've used SumIF many times and is very easy.

4. Hi Howard

Re: "The reason where it may return zero is the the account numbers appears in Sheet 1 are numbers and those on sheet "imported data" are text"

If the source data in column [A] is a 'Text account number', but the thing you are using to match (in column [C] ) is a 'numeric account number', then you can convert your lookup numeric value to text like this:
=IF(\$A1="",0,SUMIF('Imported Data'!\$A:\$A,""&\$C1,'Imported Data'!E:E))

If it had been the other way around, you can use..
=IF(\$A1="",0,SUMIF('Imported Data'!\$A:\$A,\$C1+0,'Imported Data'!E:E))

zeddy

5. Hi Zeddy

Thanks for the reply. I think we are getting closer. I have established that the numbers are text on both sheets for eg in Col C sheet1 account number '1100 and on sheet imported data '1100. The sumif as is stands gives me zero. If we can resolve this issue then it will work

6. Hi Zeddy

Attached please find sample data which will assist you in resolving the problem

Your assistance in this regard is most appreciated

Regards

Howard

7. Hi Howard

I don't see any sumif formulas?
Is it the right file??

zeddy

8. Hi Zeddy

Thanks for the reply. Please have a look at sheet1 G4-This contains a sumif formula. It returns zero, where is should return 432 (value of account 1100 on on sheet imported data)

Hope this helps

Regards

Howard

9. Hi Zeddy

I have attached the file again -see SUMIF formula in G4 on sheet1. Your assistance in resolving this is most appreciated

Regards

Howard

10. Hi Howard

Just got back.
I also had some issues with using SUMIF to get your results.
So I went back to using array formulas which worked.
See attached file.

Meanwhile, I will look again to find out what is causing the SUMIF problems.
However, I have a 300mile trip tomorrow so may take a little longer.

zeddy

11. Hi Howard

An interesting problem!
So, in the attached file, I have your sheet1 (from your file) where the SUMIF formulas do not work, as you can see in columns [G] and [I] on sheet named [Sheet1] where they all show zero values.
Now here's the interesting thing:
1. insert a new sheet, then go back to [Sheet1], copy the entire sheet (put cellpointer in cell [A1], then press Ctrl-A, then press Ctrl-C to copy entire sheet)
2. Go back to your new sheet, press Ctrl-V to paste the copied data.

You now have a copy of the sheet1, but this time the SUMIF formulas in columns [G] and [I] are now working!

Go figure!!
I don't know how you got your sheet1, but something is a bit iffy with it!!!

zeddy

12. ## The Following User Says Thank You to zeddy For This Useful Post:

HowardC (2013-04-03)

13. @ HowardC

Try cleaning your file up for starters!!

14. ## The Following User Says Thank You to Kevin@Radstock For This Useful Post:

HowardC (2013-04-03)

15. Hi Zeddy & Kevin

Thanks for your valuable input, this is much appreciated. Much have been some gremlin's on sheet1. The workbook is a file that was converted into Excel from Quattro Pro many years ago so I guess it may have caused some problems

Zeddy, hope you have a good trip

Regards

Howard

16. Hi Howard

Thanks for the explanation. Quattro Pro??? Surely you are too young for that.

zeddy

17. Quattro Pro was a program that I inherited from the person I took over from 10 years ago. I hated it and switched over to Excel within a few months of joining the company. Believe it or not or Finance Director still useds Quattro and refuses to change

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
•