Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
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 ? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
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! In article , John Moore wrote: 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 ? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
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 "John Moore" wrote in message ... 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 ? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
TRY:
=SUM(1/COUNTIF(A1:A5,A1:A5)*(B1:B5="France")) Entered with Ctrl + Shift +Enter Assumes no blanks in your data "John Moore" wrote: 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 ? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
Thanks guys ,,, worked a treat , much obliged
"Toppers" wrote: TRY: =SUM(1/COUNTIF(A1:A5,A1:A5)*(B1:B5="France")) Entered with Ctrl + Shift +Enter Assumes no blanks in your data "John Moore" wrote: 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 ? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif
On Jun 12, 9:44 am, John Moore
wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif ? | Excel Discussion (Misc queries) | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |