Need condtl formula to add A2 + A3, if A1 is a certain text. tried using .... =SUMIF(A1,"text", A2 A3) but this returns an error if A2 is blank. HELP!
meant to say the formula I tried was .... =SUMIF(A1,"text", A2+A3)
Hi Yago,
This works for me. Is your table like this?
=SUMIF(A1,"text",A2+A3) (and Fill Right)
Regards,
Ian
That works but if A2 or A3 is blank, I get an error.
Hello
What you need is not SUMIF() but combination of IF() and SUM().
E.g.,
Table 1 A1 text A2 A3 6 A4 =IF(A1="text",SUM(A2:A3),0)
Regards,
H
Hi Yago,
I can not reproduce the error.
Please post a screen shot of the relevant part of your table.
Regards,
Ian.
Hi Hiroto,
Nice solution 🙂.
But I still can't find an error with =SUMIF(A1,"text",A2+A3)
Regards,
Ian.
Hiroto / Yellowbox - The combination of IF & SUM works well, but still sometimes getting the error depending on the data format of the cell with the dollar amount. If the cell is formatted as currency, then all is well. However, if i want to remove the dollar figure so the cell is blank, I click the space bar to remove the data which causes that cell to revert to automatic format. That's when the error occurs. If I change that cell back to currency, then it works again.
Is there a type os "ERRORIS" formula that would prevent this from happening?
Yellowbox -
The combination of IF & SUM works well, but still sometimes getting the error depending on the data format of the cell with the dollar amount. If the cell is formatted as currency, then all is well. However, if i want to remove the dollar figure so the cell is blank, I click the space bar or clear-all to remove the data. That causes that cell to revert to automatic format. That's when the error occurs. If I change that cell format back to currency, then the formula works again. I need to make this user-friendly for others that will be using this sheet.
Is there a type os "ERRORIS" formula that would prevent this from happening?
Hi Yago,
I think what is happening when you click the spacebar is you are adding text- in this case a space and making the cell a text cell. This is why the format goes to automatic. When you clear the cell use the delete key. Tha should preserve your format and Numbers will treat it as a zero value.
quinn
Good Call Quinn. I'd still however love to make this dummy-proof for other co-workers who will be using my sheet, in case they make a similar mistake. If there's an ERRORIS I can add to my formula, that would be great.
SUM will ignore text cells; it is the "+" operand that throws the error. So Hiroto's solution using SUM(A2;A3) [or else SUM(A2,A3)] instead of A2 + A3 should work.
HI Yago,
To answer your question from Jun 5, 2015
Is there a type os "ERRORIS" formula that would prevent this from happening?
And Jun 7, 2015
I'd still however love to make this dummy-proof for other co-workers who will be using my sheet
Yes, you can. Wrap your formula inside the IFERROR function.
However, that will make it difficult to "debug" the errors. Errors will be hidden and your co-workers can then enter wrong data without an error warning.
Regards,
Ian.
Hi Yago,
Repeat from June 2:
Yellowbox wrote on June 2 (Eastern Australian Time):
I can not reproduce the error.
Please post a screen shot of the relevant part of your table.
Please reply with a screen shot of a small part of your screen (command shift 4 and drag). Full screen shots (command shift 3) are often difficult to read.
Wayne Contello has written a User Tip on how to post a screen shot.
https://discussions.apple.com/docs/DOC-6591
Regards,
Ian.
Hello
The formula:
=SUM(A2:A3)
and the formula:
=A2+A3
are two things.
As already noted, SUM() silently ignores text values.
E.g.,
Regards,
H
conditional formula returns error due to blank cell
