Excel Formula - Counting Multiple Lines & Duplicates

[PD9]

Tried another way - still counting/summing values as total number of lines. Bugger.

Edited by PD9 on Monday 1st October 11:26

[PD9]

Having a problem in excel where I’m failing to find a formula to count duplicate values as 1 order.

I want to count the order numbers in Column A, so that the quantity in Column B displays the value as 1. For example; Row 2 is a 1 line order so should show as a Quantity of 1, Row 3-5 is a 3 line order (same order number) so I only want to count the first line (Row 3) as one and count the duplicates (Row 4 & 5) as zero. I’ve manually added a value to show how this should look in Column B. Is there any way to do this? I can’t seem to get the IF FREQUENCY to work, it’s counting overall orders which is correct for the sample at 6 (Column C) but this isn’t what I want. Other than working through manually I can’t get this quickly. I can get the same through a pivot but there is data behind which I need to relate to global regions and split hence I need each order count to be unique and can’t look at overall, then I will be able to pivot based on this.



Any help appreciated!

Edited by PD9 on Monday 1st October 11:27

[Mr Overheads]

In cell C2 put

=IF(A2=A1,"0","1")*1

Then drag it down column C. The *1 is on the end so it formats it as a number, not as text.

[PD9]

Mr Overheads said:

In cell C2 put

=IF(A2=A1,"0","1")*1

Then drag it down column C. The *1 is on the end so it formats it as a number, not as text.

Genius! Worked a treat - so simple!



Many thanks.