Im working with a data sheet and I have a column with multiple customers, every customer might appear more than once. Is there a way for excel to check the column, find the cells that match, and then execute a formula?
My Column could look like this:
Apple
Apple
Intel
Windows
Intel
I want excel to check the column for duplicates and calculate an average for each customer. Like an average for both apple lines, then a new for both Intel, and a new for Windows.
CodePudding user response：
This can be handled using the formula =COUNTIF(A$2:A2,A2), as in following example:
As you see, every item if counted from the beginning (A$2) up to itself (for cell "B6", the formula has become =COUNTIF(A$2:A6,A6)).
Explanation:
- Go in every cell from "A$2" to "A6".
- Count the amount of times you find the value of "A6".
- The usage of absolute cell references (with the dollarsign) and relative cell references (without the dollarsign) becomes clear once you drag the formula down for new cell entries.
You can use this as a start for an =IF()-based formula.
CodePudding user response：
Okay then what if my table looks like this?
I would like excel to perform an "average" formular once for each customer. If there are three of the same customer I would like excel to calculate the average visitor of those and then have excel do that for the whole table
