Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count values with conditions
Hi!
I have a sheet with 3 rows (A- date; B-first_name; C-last_name) - see http://users.kiss.si/~k4ef2308/images/1.jpg for screenshot. I would like to count a number of (for example) cells with values John (in row B ) Smith (in row C) for a certain period, let's say April (row A). I need a review for certain people and for certain period. See http://users.kiss.si/~k4ef2308/images/2.jpg for screenshot. How should I write formula? Tnx, Elvis |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count values with conditions
try this one:
=COUNT(IF((date=A2:A20)*(B2:B20="John")*(C2:C20=" smith"),1)) change date to which date you want. Example for April month: =COUNT(IF((A2:A20=DATEVALUE("4/1/2006"))*(A2:A20<=DATEVALUE("4/30/2006"))*(B2:B20="john")*(C2:C20="smith"),1)) Note: this is Array function, so once you entered function to purticular cell, press Ctrl+Shift+Enter key "ElvisS" wrote: Hi! I have a sheet with 3 rows (A- date; B-first_name; C-last_name) - see http://users.kiss.si/~k4ef2308/images/1.jpg for screenshot. I would like to count a number of (for example) cells with values John (in row B ) Smith (in row C) for a certain period, let's say April (row A). I need a review for certain people and for certain period. See http://users.kiss.si/~k4ef2308/images/2.jpg for screenshot. How should I write formula? Tnx, Elvis |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count values with conditions
Try:
=SUMPRODUCT(--(B2:B100="John"),--(C2:C100="Smith"),--(Month(A2:A100)=4)) The "John" , "Smith" and "4" (April) can be cell references. =SUMPRODUCT(--(B2:B100)X1),--(C2:C100=X2),--(Month(A2:A100)=X3)) I received an error when trying to view your 2.jpg file so I was unable to see your report format. HTH "ElvisS" wrote: Hi! I have a sheet with 3 rows (A- date; B-first_name; C-last_name) - see http://users.kiss.si/~k4ef2308/images/1.jpg for screenshot. I would like to count a number of (for example) cells with values John (in row B ) Smith (in row C) for a certain period, let's say April (row A). I need a review for certain people and for certain period. See http://users.kiss.si/~k4ef2308/images/2.jpg for screenshot. How should I write formula? Tnx, Elvis |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count values with conditions
=SUMPRODUCT(--(MONTH(A2:A20)=4),--(B2:B20="John"),--(C2:C20="Smith"))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "ElvisS" wrote in message ups.com... Hi! I have a sheet with 3 rows (A- date; B-first_name; C-last_name) - see http://users.kiss.si/~k4ef2308/images/1.jpg for screenshot. I would like to count a number of (for example) cells with values John (in row B ) Smith (in row C) for a certain period, let's say April (row A). I need a review for certain people and for certain period. See http://users.kiss.si/~k4ef2308/images/2.jpg for screenshot. How should I write formula? Tnx, Elvis |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count values with conditions
....typo ....
=SUMPRODUCT(--(B2:B100=X1),--(C2:C100=X2),--(Month(A2:A100)=X3)) "Toppers" wrote: Try: =SUMPRODUCT(--(B2:B100="John"),--(C2:C100="Smith"),--(Month(A2:A100)=4)) The "John" , "Smith" and "4" (April) can be cell references. =SUMPRODUCT(--(B2:B100)X1),--(C2:C100=X2),--(Month(A2:A100)=X3)) I received an error when trying to view your 2.jpg file so I was unable to see your report format. HTH "ElvisS" wrote: Hi! I have a sheet with 3 rows (A- date; B-first_name; C-last_name) - see http://users.kiss.si/~k4ef2308/images/1.jpg for screenshot. I would like to count a number of (for example) cells with values John (in row B ) Smith (in row C) for a certain period, let's say April (row A). I need a review for certain people and for certain period. See http://users.kiss.si/~k4ef2308/images/2.jpg for screenshot. How should I write formula? Tnx, Elvis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Search, Count, Match and Post Values | Excel Worksheet Functions | |||
get a single count after looking up a set of values | Excel Worksheet Functions | |||
How to count the number of 'values' in a cell | Excel Worksheet Functions | |||
Getting Count field to recognise rows with negative values in Exc. | Excel Worksheet Functions | |||
Count Unique Values | Excel Worksheet Functions |