Countif

[John Moore]

Hi guys, I am trying to use COUNTIF function to return data based on 2
criteria ...

A B C
1 12345 France 10
2 23456 UK 15
3 12345 France 20

What I am looking for is for the COUNTIF to return the number of orders for
FRANCE e.g. ( from the table above the answer would be 2 ) but, the problem I
ahve is that the order number ( column A ) is the same for rows 1 and 3 ,,,
so , effectively it's the same order , just different lines and qty's (
column C ) ,, so the answer I need is 1 ,,, is there a way to do this using
COUNTIF and perhaps VLOOKUP ?

 

[Domenic]

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(B2:B100="France",A2:A100),IF(B2:B100="France",A2:A10
0)),1))

Hope this helps!

 

[Bernie Deitrick]

John,

Enter using Ctrl-Shift-Enter:

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

When you expand the ranges, make sure that each range has the same number of rows.

HTH,
Bernie
MS Excel MVP

 

[Toppers]

TRY:

=SUM(1/COUNTIF(A1:A5,A1:A5)*(B1:B5="France"))

Entered with Ctrl + Shift +Enter

Assumes no blanks in your data

 

[John Moore]

Thanks guys ,,, worked a treat , much obliged

TRY:

=SUM(1/COUNTIF(A1:A5,A1:A5)*(B1:B5="France"))

Entered with Ctrl + Shift +Enter

Assumes no blanks in your data

 

[SpreadsheetBrian]

Hi guys, I am trying to use COUNTIF function to return data based on 2
criteria ...

A B C
1 12345 France 10
2 23456 UK 15
3 12345 France 20

What I am looking for is for the COUNTIF to return the number of orders for
FRANCE e.g. ( from the table above the answer would be 2 ) but, the problem I
ahve is that the order number ( column A ) is the same for rows 1 and 3 ,,,
so , effectively it's the same order , just different lines and qty's (
column C ) ,, so the answer I need is 1 ,,, is there a way to do this using
COUNTIF and perhaps VLOOKUP ?

Another technique is to essentially create a composite key field

D1 := "=CONCATENATE($A1,$B1)"

A B C D
1 12345 France 10 12345France
2 23456 UK 15 23456UK
3 12345 France 20 12345France

and simply use COUNTIF() on the D column

Column D can then be hidden or muted with gray text to minimize its
appearance.
This lets you use your spreadsheet more like a database and you don't
have so many parentheses in your formulas.

Brian Herbert Withun