COUNTIF formula with reference to cell above

**pri31** · 07-02-2013, 07:38 AM

Hi All,

I am creating a table with emails exported from outlook with subject, received, sender as the headings. In the process of counting the the subjects lines for each one but to exclude the count if the above cell contains the same (or close match) text.

=COUNTIF(A1:A25,INDIRECT(ADDRESS(ROW()-1,COLUMN())))

Have a feeling that I am completely off the target!

I am a beginner for excel and comfortable with just the basics and would appreciate any help.

Kind Regards

Priyesh

**dskysmineb** · 07-02-2013, 07:51 AM

I believe with just formulas it might be complicated. They don't seem to be ordered at all so if there are multiple cases of the same subject in different days a countif formula would miss it. If you don't want to use VBA you can select the whole Table and insert a Pivot Table with Row Label "Subject" and Values as "Count of Received".
Note though that this will use exact differences. So if you would want to aggregate "Re: Blackberry" and "TR: Blackberry" and "Re: Blackberry " in the same unique instance you would not be able to do it this easily.

Kind regards,
D.

**pri31** · 07-02-2013, 07:59 AM

I can get rid of the RE and FW quite easily but this is just a small extract from a much larger table. I only want it to discard the count if the "above cell" to the reference is a match.

The aim is to find how many times calls are raised removing the conversations so that it is only recorded once. I think it will be an impossible task without complicated VB coding. Really do appreciate your help.

Kind Regards

Priyesh

**dskysmineb** · 07-02-2013, 08:07 AM

True you can get rid of the "Re:" and "FW:", but for example in the file you sent there is one instance of "Re: Blackberry" and "Re: Blackberry " where they only differ by one space. If you are comparing exact matches these would be considered different.
Also why do you say if only the above reference is a match? If someone replies 2 hours later to "Re:Blackberry" it will still be in the same conversation even if there are other emails that came in the meantime right?
What do you mean by "Calls raised"?
Maybe if you explain a little better what the purpose is we can understand what is the best way to get what you need.

Kind regards,
D.