Countif Query

[John Moore]

Let me see if I can explain this,,,,,I want to
count the number of orders that show as SHIP but I only want to count them
once if the order number in column A appears more than once...... so I want
to be able to count the number of SHIPS in column B but only count them once
if the order number in column A appears more than once ,,,,, so the below
would return an answer of 2

A B
1 123456 SHIP
2 123456 SHIP
3 145799 NOT
4 145799 SHIP

 

[Toppers]

Enter with Ctrl+Shift+Enter

=IF(B1:B4="SHIP",SUM(1/(COUNTIF(A1:A4,A1:A4))))

 

[John Moore]

Hi Toppers, I get a DIV error with this

Enter with Ctrl+Shift+Enter

=IF(B1:B4="SHIP",SUM(1/(COUNTIF(A1:A4,A1:A4))))

 

[Peo Sjoblom]

There can be no blank cells included, if so use

replace

SUM(1/(COUNTIF(A1:A4,A1:A4))

with

SUMPRODUCT(--(A1:A4<>""),1/COUNTIF(A1:A4,A1:A4&""))



--
Regards,

Peo Sjoblom

 

[driller]

John Moore,

count the number of *orders*

if the answer was 2,
for which lines of data we shall count
1 and 2
1 and 4
or 2 and 4

regards,
driller