Counting date and catagory match

[Reed]

I want excel to return a number if there is a date match in column B and a
Category Name match in column C. I want to be able to do it for the whole
column because the amount of items that occur on certain dates tend to
fluctuates.

 

[JMB]

to return something from column D, for example
=INDEX(D1100, MATCH(1, (B1:B100=SomeDate)*(C1:C100=SomeCategory), 0))

entered w/ Ctrl+Shift+Enter. if done properly, XL will put braces { }
around the formula.

 

[JMB]

looking at your subject header, do you want to count how many items match a
particular date and category??

=SUMPRODUCT(--(B1:B100=SomeDate), --(C1:C100=SomeCategory))

 

[Reed]

Hello,

Yes thats correct, like B will be 11/1/07 a few times, then 11/7/07 and so
on, then C will be the name of a certain category. I just want to be able to
do this for the whole of B not just a little section of it because the number
of 11/07s may change as the month goes on

 

[JMB]

you could adjust the ranges to suit your needs
=SUMPRODUCT(--(B1:B65535=SomeDate), --(C1:C65535=SomeCategory))

Prior to XL 2007, sumproduct cannot work on entire columns (so B:B and
B1:B65536 will not work).

 

[Reed]

So like? =SUMPRODUCT((B1:B100=11/1/2007), (C1:C100=Referral))

this report an error

 

[JMB]

Couple of issues:
First - you omitted the double unary operator -- (it's use is explained in
the link below)
Second - XL treats 11/1/2007 as 11 divided by 1 divided by 2007 when entered
in this manner. XL stores dates as numbers. 11/1/2007 is the result of a
format that is applied to the number so it makes sense to us humans.
Third - is referral a named range? If not and you are searching for the
word referral, it needs to be in quotes.

I expect this should work:
=SUMPRODUCT(--(B1:B100=DATE(2007, 11, 1)), --(C1:C100="Referral"))

or use cell references for the desired date and category
=SUMPRODUCT(--(B1:B100=D1)), --(C1:C100=E1))

more on using sumproduct for multiple condition tests here:
http://xldynamic.com/source/xld.SUMPRODUCT.html