Solved Calculating from IF statements

March 8, 2012 at 15:00:10
Specs: Windows 7, AMD X2 4800+ 4GB
I am trying to sum the results from a group of IF statements below is an example of what they are doing.

The first statement is as follows for cell K4;

"=IF(B4=B30,"10=IF(B4=C30,"3",IF(B4=D30,"3","0")))" currently in the spreadsheet the first condition is met so 10 is displayed in the cell.

Similar statements are statements are written for cells L4 through Q4 with different results.

Where I run into difficulty is when try to sum the totals of K4 through Q4. When I attempt to do this using the usual methods =SUM(K4:Q4), =SUM(K4+L4+....Q4) of coming up with the sum of multiple celIs get an output of 0.

If the answer is already out there my search skills must be failing. Any assistance would be greatly appreciated, to include pointing me in the direction of the answer if it has already been given somewhere alse.

See More: Calculating from IF statements

Report •

March 8, 2012 at 17:44:33
✔ Best Answer
First, your formula makes no sense. The double quote before the 10 and the equal sign after it should not be there. All they do is cause an error.

That said, you are probably getting a SUM of 0 because all the values that your formula will return (if it was written correctly) are enclosed in quotes. That makes Excel see them as text, not as numbers.

"3" is not equal to 3 since "3" is the text string 3 and 3 is the number 3.

Look at your cells. If the value is left justified (by default) then it's text, not a number.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 9, 2012 at 07:34:34
Thanks for the reply.

I had mistyped the formula in the post, but you are smart enough to see through my mistakes and find an answer for me. Once you mentioned the values being text and not a number the light bulb went off.

Report •

March 9, 2012 at 08:04:54
Glad I could help.

re: "I had mistyped the formula in the post..."

I often suggest that formulas be copied directly from the formula bar and pasted into a post instead of re-typing them. For one thing, why bother? Copy...Paste is so much easier. More importantly, the exact formula that is giving you problems will be in the post with no possibility of a typo.

Just as an aside, if you are ever stuck with a list of "text numbers" that won't act like numbers in Excel, you can try a few things to convert them to numbers.

Many times, performing any type of mathematical operation on the text will convert it to a number.

For example, if you Copy an empty cell and then do a PasteSpecial...Add to your text cells, it will try to add 0 to the "text" and convert it to a number.

Of course, that wouldn't have worked in your case since the text was the result of a formula, but often times "numbers" that are download from the web or copied from another app often end up as text and you can't do anything with them until they are converted to numbers.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

Ask Question