Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting when both match | Excel Worksheet Functions | |||
Catagory Axis | Charts and Charting in Excel | |||
I AM TRYING TO ADD INFO TO THE CATAGORY AXIS BUT IT WONT LET ME C. | Charts and Charting in Excel | |||
How do I change the scale of the catagory axis? | Charts and Charting in Excel | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions |