Counting 2 columns

[want to learn]

I need your help!
I have a column (Lets say A) with dates in it and a column (Lets say B) with names in it. I want a formula for counting column A if it = "certain date" and column B if it is = "certain name. Ex, How many times on 8/1/2007 did "john" appear.
Thanks

 

[JazzSP8]

Hi

Probably needs to change to make it a little more dynamic, but I created this with the Conditonal Sum wizard...

New Microsoft Excel Worksheet.xls
	A	B	C	D
1	Date	Name	Count
2	01/01/2007	John	2
3	01/01/2007	John
4	02/01/2007	John
5	03/01/2007	Mike
6	04/01/2007	MIke
7	05/01/2007	John
8	05/01/2007	John
Sheet1

HTH

 

[arthurbr]

You can also try the SUMPRODUCT fucntion

=sumproduct((your_range="date")*(other_range="john"))

More info on how to use this function here :http://www.xldynamic.com/source/xld.SUMPRODUCT.html#classic

hth

 

[want to learn]

That did not work JazzSP8. I get #VALUE!

 

[JazzSP8]

It does work, you can see the example above...

Did you CTRL, SHIFT & ENTER the formula in C2? (See the { }'s?)

 

[want to learn]

I do not understand the CTRL, SHIFT & ENTER but i did remove the {}'s.
I believe you, it does work on yours.
Here is what I have. =SUM(IF($A$2:$A$8=DATEVALUE("1/1/2007"),IF($B$2:$B$8="John",1,0),0))

 

[Jon von der Heyden]

Try:

=SUMPRODUCT((TEXT($A$2:$A$8,"dd/mm/yy")="01/01/07")^1,($B$2:$B$8="John")^1)

This doesn't require ctrl + shift + enter

Regards
Jon

 

[JazzSP8]

Entering a formula with CTRL, SHIFT & ENTER (instead of just ENTER) creates an array formula and encloses it in chevrons ({}'s) which are needed for the formula to work...

Learn more about array formulas here

If your going to need a lot of formulas then you maybe better off trying one of the SUMPRODUCT soloutions though...

 

[want to learn]

Thanks, Jon von der Heyden That worked.

JazzSP8,
I still would like to know what I am doing wrong on the other formula.

 

[JazzSP8]

See above in case you missed it, we posted at the same time