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

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

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

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

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