Counting date and catagory match
 

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.

Counting date and catagory match
 

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

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


"Reed" wrote:

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.

Counting date and catagory match
 

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))



"JMB" wrote:

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

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


"Reed" wrote:

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.

Counting date and catagory match
 

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" wrote:

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))



"JMB" wrote:

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

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


"Reed" wrote:

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.

Counting date and catagory match
 

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" wrote:

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" wrote:

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))



"JMB" wrote:

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

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


"Reed" wrote:

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.

Counting date and catagory match
 

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

this report an error

"JMB" wrote:

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" wrote:

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" wrote:

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))



"JMB" wrote:

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

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


"Reed" wrote:

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.

Counting date and catagory match
 

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 he
http://xldynamic.com/source/xld.SUMPRODUCT.html


"Reed" wrote:

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

this report an error

"JMB" wrote:

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" wrote:

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" wrote:

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))



"JMB" wrote:

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

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


"Reed" wrote:

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.