Excel sum formula problem

March 26, 2009 at 10:48:17
Specs: Windows Vista
I have a bit of an excel problem

I want to do the sum of cells which contain if formulas.

But if i do a sum of them it returns zero

So i need some method to do a sum of the values of these cells.

Help would be great


See More: Excel sum formula problem

Report •

March 26, 2009 at 10:59:32
Please don't take this the wrong way, but go back and read your post. Assume you know nothing about your spreadsheet or the formulae used.

Do you see enough information in the post for us to offer any help?

Please give us some more details and maybe some examples of the formulae and I'm sure that we can offer some assitance.

Report •

March 26, 2009 at 14:32:38
Ok so sorry I wrote the post in a hurry, here are some more details:

In cell AD2 I have the formula =IF((Del.!G13=1)*AND(Del.!B13=101)*AND(Del.!E13=X2),Del.!L11,"")
and in AD3 =IF((Del.!G14=1)*AND(Del.!B14=101)*AND(Del.!E14=X3),Del.!L12,"")

and so on down to Ad19

and in AD20 i want a sum formula of the values of the results all the if formulas give, bearing in mind not all of them will have a results.

But having tried this all I get from the sum formula is the value 0.

Any help please?

Thanks :)

Report •

March 26, 2009 at 15:13:07
Seems to work for me.

Let me ask you 2 questions:

1 - With regards to the X2, X3, X4, which sheet are the formulae supposed to be looking at?

As written, the formulae are looking at the same sheet as where the formulae are, not at Del. Maybe that's why it didn't work for you.

2 - This has nothing to do with your question, but I'm just curious as to why you used the formula construction you did. I would have written the formula as follows:


While the results should be the same, you appear to the using the AND function just to produce a 1, not to actually compare any arguments.

Report •

Related Solutions

March 27, 2009 at 09:41:13
thanks for your attempt to help, I used the formula you wrote but the sum still returns 0.

The only way I can get a total of all the cells is to do =AD2+AD3 etc. but if any cells are blank that doesn't work either and entering each cell to add up seperately will take bit longer than wanted.

So any other suggestions?


Report •

March 27, 2009 at 10:05:02
Like I said in my response, I was able to get a sum of the values in column AD using your formulae. I was also able to get them to sum using mine, so that's not the issue.

I simply put =SUM(AD2:AD19) in AD20 and the sum changed based on whether or not the IF statements were true or not - even with blank cells in the range.

Obviously I can't see your spreadsheet from here, so without the actual sheet I can't tell you what's wrong.

I've sent you a PM with an email address. if you send the workbook to that address, I'll take a look at it.

Report •

Ask Question