Count how many entrances in column A have different entrances in column B?

[SofiaV]

Hi all,

I'm trying to find a formula that counts the entrances in a column that have different entrances in the next column.

For example, if I count how many ship in column "Ship" have Charged and Discharged the result must be 2 (Ships T and B)

Thanks in advance,
Sofia

Ship	Charges/Discharge
T	C
B	D
S	C
E	C
E	C
E	C
E	C
E	C
E	C
E	C
I	C
B	C
T	D

 

[jasonb75]

Noting that ship 'E' has 7 charges and 0 discharges, if you subsequently add 4 discharges 4 ship 'E' to the list, should that be counted as
4 (count once for each pair of charge and discharge)?
1 (count once because all are the same ship)?
0 (don't count because there are unequal entries for charge and discharge)?
Or something completely different?

Similarly, what if there were 7 charges and 7 discharges for ship 'E', how should it be counted?

 

[maabadi]

I think you can only Discharded at second Column.

=COUNTIF($B$2:$B$14,"D")

 

[jasonb75]

@maabadi's suggestion above will count all discharge entries (multiple entries for the same ship will all be counted), this matches up to the first query that I mentioned in my earlier reply.

This formula will count unique discharge entries (a ship discharged multiple times will only be counted once), this matches up to the second query that I mentioned in my earlier reply.

=ROWS(UNIQUE(FILTER(A2:B14,B2:B14="D")))

If you need a formula that matches up to the third query in my earlier reply, or something else entirely then that will need something a little more creative.

Note that neither formula will catch erroneous entries, in the event that a ship is discharged without charge it will still be counted.

With many things in excel formulas, it is the small details that make the big difference between something that works and something that works properly.

 

[SofiaV]

Noting that ship 'E' has 7 charges and 0 discharges, if you subsequently add 4 discharges 4 ship 'E' to the list, should that be counted as
4 (count once for each pair of charge and discharge)?
1 (count once because all are the same ship)?
0 (don't count because there are unequal entries for charge and discharge)?
Or something completely different?

Similarly, what if there were 7 charges and 7 discharges for ship 'E', how should it be counted?

Hi Jasonb,

Thank you for your help. For now, I would like the result to be 1 because I need to know How many ships have made different movements.

 

[jasonb75]

In that case the formula in post 4 should do what you need.