| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF with multiple criteria doesn't work
Hi all,
I'm trying to find the number of match value in a range of cell by using multiple criteria,but can not get the result that I want.I use COUNTIF function like below but doesn't work: =COUNTIF(A:E,AND(A2,B2,C2,D2,E2)) How to solve it.The function always return 0 (zero)although there are similar value.Bu if I put single criteria,it works fine. I work with Excel 2000 and the data type for each column is: Column A : Date Column B : Text Column C : Text Column D : Number Column E : Number Please helphow to corrected the formula. Rgds, Shiro. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF with multiple criteria doesn't work
Try this:
A2:E2 are your criteria: A2 = some date B2 = some text value C2 = some text value D2 = some number E2 = some number =SUMPRODUCT(--(A3:A100=A2),--(B3:B100=B2),--(C3:C100=C2),--(D3:D100=D2),--(E3:E100=E2)) Note that you can't use entire columns as range references in SUMPRODUCT unless you're using Excel 2007. -- Biff Microsoft Excel MVP "shiro" wrote in message ... Hi all, I'm trying to find the number of match value in a range of cell by using multiple criteria,but can not get the result that I want.I use COUNTIF function like below but doesn't work: =COUNTIF(A:E,AND(A2,B2,C2,D2,E2)) How to solve it.The function always return 0 (zero)although there are similar value.Bu if I put single criteria,it works fine. I work with Excel 2000 and the data type for each column is: Column A : Date Column B : Text Column C : Text Column D : Number Column E : Number Please helphow to corrected the formula. Rgds, Shiro. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF with multiple criteria doesn't work
Dear Mr.T.Valko,
It still doesn't return the value that I want.May be I describe my situation unclearly,but thank's for response. Let see my sample below data. Date Status Area code Area No Serial No Repeated data 4/29/2008 OK J5 818 125 4/30/2008 OK J5 818 126 5/1/2008 NG J5 818 127 5/2/2008 OK J5 818 128 5/3/2008 OK J5 818 129 5/4/2008 NG J5 818 130 5/5/2008 OK J5 818 131 5/6/2008 OK J5 818 132 4/29/2008 NG J5 818 133 4/30/2008 OK J5 818 134 5/1/2008 OK J5 818 135 5/2/2008 NG J5 818 136 5/3/2008 OK J5 818 129 5/4/2008 OK J5 818 122 5/5/2008 NG J5 818 115 5/6/2008 OK J5 818 108 Actually there are 2 excatly similar data,how to insert the formula in the column/cell "Repeated data",so that it return 2. thank's in advance. Rgds, Shiro "T. Valko" wrote in message ... Try this: A2:E2 are your criteria: A2 = some date B2 = some text value C2 = some text value D2 = some number E2 = some number =SUMPRODUCT(--(A3:A100=A2),--(B3:B100=B2),--(C3:C100=C2),--(D3:D100=D2),--(E 3:E100=E2)) Note that you can't use entire columns as range references in SUMPRODUCT unless you're using Excel 2007. -- Biff Microsoft Excel MVP "shiro" wrote in message ... Hi all, I'm trying to find the number of match value in a range of cell by using multiple criteria,but can not get the result that I want.I use COUNTIF function like below but doesn't work: =COUNTIF(A:E,AND(A2,B2,C2,D2,E2)) How to solve it.The function always return 0 (zero)although there are similar value.Bu if I put single criteria,it works fine. I work with Excel 2000 and the data type for each column is: Column A : Date Column B : Text Column C : Text Column D : Number Column E : Number Please helphow to corrected the formula. Rgds, Shiro. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF with multiple criteria doesn't work
=SUM(IF((COUNTIF(A2:A17,A2:A17)1)*(COUNTIF(B2:B17 ,B2:B17)1)*(COUNTIF(D2:D17,D2:D17)1)*(COUNTIF(E2 :E17,E2:E17)1),1))
ctrl+shift+enter, not just enter "shiro" wrote: Dear Mr.T.Valko, It still doesn't return the value that I want.May be I describe my situation unclearly,but thank's for response. Let see my sample below data. Date Status Area code Area No Serial No Repeated data 4/29/2008 OK J5 818 125 4/30/2008 OK J5 818 126 5/1/2008 NG J5 818 127 5/2/2008 OK J5 818 128 5/3/2008 OK J5 818 129 5/4/2008 NG J5 818 130 5/5/2008 OK J5 818 131 5/6/2008 OK J5 818 132 4/29/2008 NG J5 818 133 4/30/2008 OK J5 818 134 5/1/2008 OK J5 818 135 5/2/2008 NG J5 818 136 5/3/2008 OK J5 818 129 5/4/2008 OK J5 818 122 5/5/2008 NG J5 818 115 5/6/2008 OK J5 818 108 Actually there are 2 excatly similar data,how to insert the formula in the column/cell "Repeated data",so that it return 2. thank's in advance. Rgds, Shiro "T. Valko" wrote in message ... Try this: A2:E2 are your criteria: A2 = some date B2 = some text value C2 = some text value D2 = some number E2 = some number =SUMPRODUCT(--(A3:A100=A2),--(B3:B100=B2),--(C3:C100=C2),--(D3:D100=D2),--(E 3:E100=E2)) Note that you can't use entire columns as range references in SUMPRODUCT unless you're using Excel 2007. -- Biff Microsoft Excel MVP "shiro" wrote in message ... Hi all, I'm trying to find the number of match value in a range of cell by using multiple criteria,but can not get the result that I want.I use COUNTIF function like below but doesn't work: =COUNTIF(A:E,AND(A2,B2,C2,D2,E2)) How to solve it.The function always return 0 (zero)although there are similar value.Bu if I put single criteria,it works fine. I work with Excel 2000 and the data type for each column is: Column A : Date Column B : Text Column C : Text Column D : Number Column E : Number Please helphow to corrected the formula. Rgds, Shiro. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF with multiple criteria doesn't work
Maybe you shoudl take a look at these tutorials:
http://www.contextures.com/xlPivot02.html http://peltiertech.com/Excel/Pivots/pivottables.htm Maybe those will give you some ideas as to what you can do with your data. Regards, Ryan--- -- RyGuy "shiro" wrote: Dear Mr.T.Valko, It still doesn't return the value that I want.May be I describe my situation unclearly,but thank's for response. Let see my sample below data. Date Status Area code Area No Serial No Repeated data 4/29/2008 OK J5 818 125 4/30/2008 OK J5 818 126 5/1/2008 NG J5 818 127 5/2/2008 OK J5 818 128 5/3/2008 OK J5 818 129 5/4/2008 NG J5 818 130 5/5/2008 OK J5 818 131 5/6/2008 OK J5 818 132 4/29/2008 NG J5 818 133 4/30/2008 OK J5 818 134 5/1/2008 OK J5 818 135 5/2/2008 NG J5 818 136 5/3/2008 OK J5 818 129 5/4/2008 OK J5 818 122 5/5/2008 NG J5 818 115 5/6/2008 OK J5 818 108 Actually there are 2 excatly similar data,how to insert the formula in the column/cell "Repeated data",so that it return 2. thank's in advance. Rgds, Shiro "T. Valko" wrote in message ... Try this: A2:E2 are your criteria: A2 = some date B2 = some text value C2 = some text value D2 = some number E2 = some number =SUMPRODUCT(--(A3:A100=A2),--(B3:B100=B2),--(C3:C100=C2),--(D3:D100=D2),--(E 3:E100=E2)) Note that you can't use entire columns as range references in SUMPRODUCT unless you're using Excel 2007. -- Biff Microsoft Excel MVP "shiro" wrote in message ... Hi all, I'm trying to find the number of match value in a range of cell by using multiple criteria,but can not get the result that I want.I use COUNTIF function like below but doesn't work: =COUNTIF(A:E,AND(A2,B2,C2,D2,E2)) How to solve it.The function always return 0 (zero)although there are similar value.Bu if I put single criteria,it works fine. I work with Excel 2000 and the data type for each column is: Column A : Date Column B : Text Column C : Text Column D : Number Column E : Number Please helphow to corrected the formula. Rgds, Shiro. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
COUNTIF with multiple criteria doesn't work
That works as long as the serial number (column E) is unique for each date.
If there might be duplicate serial numbers for multiple dates then it returns an incorrect result. 4/29/2008 OK J5 818 129 5/3/2008 OK J5 818 129 5/3/2008 OK J5 818 129 It might be easier to just concatenate and count the dupes: =A1&B1&C1&D1&E1 =SUMPRODUCT(--(COUNTIF(rng,rng)1)) -- Biff Microsoft Excel MVP "Teethless mama" wrote in message ... =SUM(IF((COUNTIF(A2:A17,A2:A17)1)*(COUNTIF(B2:B17 ,B2:B17)1)*(COUNTIF(D2:D17,D2:D17)1)*(COUNTIF(E2 :E17,E2:E17)1),1)) ctrl+shift+enter, not just enter "shiro" wrote: Dear Mr.T.Valko, It still doesn't return the value that I want.May be I describe my situation unclearly,but thank's for response. Let see my sample below data. Date Status Area code Area No Serial No Repeated data 4/29/2008 OK J5 818 125 4/30/2008 OK J5 818 126 5/1/2008 NG J5 818 127 5/2/2008 OK J5 818 128 5/3/2008 OK J5 818 129 5/4/2008 NG J5 818 130 5/5/2008 OK J5 818 131 5/6/2008 OK J5 818 132 4/29/2008 NG J5 818 133 4/30/2008 OK J5 818 134 5/1/2008 OK J5 818 135 5/2/2008 NG J5 818 136 5/3/2008 OK J5 818 129 5/4/2008 OK J5 818 122 5/5/2008 NG J5 818 115 5/6/2008 OK J5 818 108 Actually there are 2 excatly similar data,how to insert the formula in the column/cell "Repeated data",so that it return 2. thank's in advance. Rgds, Shiro "T. Valko" wrote in message ... Try this: A2:E2 are your criteria: A2 = some date B2 = some text value C2 = some text value D2 = some number E2 = some number =SUMPRODUCT(--(A3:A100=A2),--(B3:B100=B2),--(C3:C100=C2),--(D3:D100=D2),--(E 3:E100=E2)) Note that you can't use entire columns as range references in SUMPRODUCT unless you're using Excel 2007. -- Biff Microsoft Excel MVP "shiro" wrote in message ... Hi all, I'm trying to find the number of match value in a range of cell by using multiple criteria,but can not get the result that I want.I use COUNTIF function like below but doesn't work: =COUNTIF(A:E,AND(A2,B2,C2,D2,E2)) How to solve it.The function always return 0 (zero)although there are similar value.Bu if I put single criteria,it works fine. I work with Excel 2000 and the data type for each column is: Column A : Date Column B : Text Column C : Text Column D : Number Column E : Number Please helphow to corrected the formula. Rgds, Shiro. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nesting COUNTIF for multiple criteria in multiple columns | Excel Worksheet Functions | |||
| countif, multiple criteria... | Excel Worksheet Functions | |||
| countif criteria only one word of few doesn't work | Excel Worksheet Functions | |||
| Countif w/ Multiple Criteria | Excel Worksheet Functions | |||
| Countif with multiple criteria and multiple worksheets | Excel Worksheet Functions | |||
![shiro[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
