Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
db count if...
hello,
I got this data: Name AK COD data pino 16 PD 1302 pino 21 PC 1402 pino 16 MI 1602 pino 16 MI 1602 pino 16 CHI 1602 pino 03 MI 1602 ciccio 12 MZ 1302 luise 04 MZ2 1302 luise 41 MZ2 1302 I wish count for the first time each number of "name", and then how many value it has for each limitation selectioned. I don't wanna use filter on becouse the data are thousands and I have to count hundreds of "name". So for example, If I wish count: how many "PINO" WITH AK=16 and COD=MI the result is: TOT PINO(for example in H2)= 6 and Pino(with AK=16 and COD=MI)=2 and Pino(with AK=16 )=4 Please help me . I don't know how I can solve this trouble. thanks a lot for your help. ----- Andrew |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
db count if...
For how many "PINO" WITH
AK=16 and COD=MI use =SUMPRODUCT(--(A1:A1000="PINO"),--(B1:B1000=16),--(C1:C1000="MI")) Change 1000 to the last row in your data set. Instead of "PINO" you can use A2 which contains PINO, instead of 16 you can use B2 which contains 16 and so on... For how many "PINO" WITH AK=16 use =SUMPRODUCT(--(A1:A1000="PINO"),--(B1:B1000=16)) "AND" wrote: hello, I got this data: Name AK COD data pino 16 PD 1302 pino 21 PC 1402 pino 16 MI 1602 pino 16 MI 1602 pino 16 CHI 1602 pino 03 MI 1602 ciccio 12 MZ 1302 luise 04 MZ2 1302 luise 41 MZ2 1302 I wish count for the first time each number of "name", and then how many value it has for each limitation selectioned. I don't wanna use filter on becouse the data are thousands and I have to count hundreds of "name". So for example, If I wish count: how many "PINO" WITH AK=16 and COD=MI the result is: TOT PINO(for example in H2)= 6 and Pino(with AK=16 and COD=MI)=2 and Pino(with AK=16 )=4 Please help me . I don't know how I can solve this trouble. thanks a lot for your help. ----- Andrew |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
db count if...
First of all, highlight all the data (including the header row), then
click on Insert | Name | Create. Ensure that only Top Row is checked, then click OK - this will have named the ranges, so it is a bit easier to work with than range references. Then you can use SUMPRODUCT like this: =SUMPRODUCT(--(Name="Pino")) to tell you how many records have Pino in the Name column, or: =SUMPRODUCT(--(Name="Pino"),--(AK=16)) to give you a count of Name containing Pino with AK column containing 16, or: =SUMPRODUCT(--(Name="Pino"),--(AK=16),--(COD="MI")) to count how many Names have Pino with AK set to 16 and COD column containing MI. You could put Pino in H1, 16 in H2 and MI in H3, for example, and then the last one becomes: =SUMPRODUCT(--(Name=H1),--(AK=H2),--(COD=H3)) then you can vary the entries in H1:H3 without having to amend the formula. Hope this helps. Pete On Feb 17, 7:49*pm, AND wrote: hello, I got this data: Name * *AK * * *COD * * data pino * *16 * * *PD * * *1302 pino * *21 * * *PC * * *1402 pino * *16 * * *MI * * *1602 pino * *16 * * *MI * * *1602 pino * *16 * * *CHI * * 1602 pino * *03 * * *MI * * *1602 ciccio *12 * * *MZ * * *1302 luise * 04 * * *MZ2 * * 1302 luise * 41 * * *MZ2 * * 1302 I wish count for the first time each number of "name", and then how many value it has for each limitation selectioned. I don't wanna use filter on becouse the data are thousands and I have to count hundreds of "name". So for example, If I wish count: how many "PINO" WITH AK=16 * * and COD=MI the result is: * * TOT PINO(for example in H2)= 6 and Pino(with AK=16 *and COD=MI)=2 and Pino(with AK=16 *)=4 Please help *me . I don't know how I can solve this trouble. thanks a lot for your help. ----- Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of 2 Numeric values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |