• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Would an IF formula work?

laurennic

New Member
I have imported my cell phone records into excel and want to distinguish percentage of business phone calls for tax purposes.


Is there a way to write a formula based on recognition of a name or number in one column to leave a notation in another column, to be counted in COUNTA and be recorded in my percentage calculation?


ex. names/numbers would be in COLUMN E and I want a "-" left in COLUMN N to indicate it's a business call.


(I have a COUNTA formula set up in Column N as of now and have been manually marking each call).


Thank you!!!
 
Hi laurennic,


Welcome to the forums!! Please provide some sample data with desired output for your problem.


Regards,
 
Hello and thank you.

I hope I can accurately give data.


I have a chart with info in columns A-M.

The rows vary based on number of phone calls made per month.

In Column N, I have the total number of calls made.

Below, I have a COUNTA formula set up to calculate the number of times "-" appears in Column N, to indicate it is a business call.


Column E contains Names of People or Phone Numbers.

(If I recognized the number, I gave it a name).


I want a formula that recognizes a name in Column E to determine if it can be marked as Business, in Column N.


Does that make sense?


It occurred to me that if I were to make a master call sheet as the first tab of the workbook, I could assign familiar contacts a number (1,2,3...) which I could then easily calculate in an IF/THEN formula. But for all of the unknown calls, it could get hectic.


Am I reaching too far?


Thanks for the help!!!!

(or at least the attempt)
 
I got a little confused on your setup, but you should be able to adapt this solution.


Let's say you have a list of Personal/Familiar numbers in col Z. To mark numbers in col N as Business numbers, formula would be:

=IF(ISNA(MATCH(N2,Z:Z,0)),"Business","Personal")
 
I'm sorry for the confusion, this is my first time asking for excel help via the internet.


What does the ISNA stand for?


The info I need recognized is in Column E, Rows 17 - 252.

If it's a business contact, I want that indicated in Column N.


If I were to create a separate sheet with the familiar info, could the formula pull from a different page?
 
ISNA is a function that check if something is the #N/A error or not, and returns a True/False output. If the MATCH function doesn't find a value, it throws the error. So, if the Match function doesn't find the value, mark it as business, otherwise, personal.


Yes, you can create a formula that references another sheet. Easiest way is to navigate to the other sheet while writing the formula and just select the cells. Or, you can write it manually using this syntax:

=IF(ISNA(MATCH(E17,'Other Sheet'!A:A,0)),"Not found","Found)
 
Back
Top