Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Finesse Needed: COUNTIF or DCOUNT or Something better
This is a basic representation for what the data I have to classify/valdate
looks like. Flavor Date Passion Peach 1/1/2009 Mango Brandy 1/1/2009 Mango 1/2/2009 Peach 1/3/2009 Acai 1/8/2009 Coconut Rum 1/19/2009 Coconut 3/7/2009 Coconut Rum 3/21/2009 Mango Brandy 7/14/2009 Passion Fruit 7/14/2009 Acai Nut 9/9/2009 I am trying to find the tightest way to show during a 1 month period how much of each flavor was purchased. Only the primary flavor counts. I am hoping to get my data to look like this: Flavor Jan Feb Mar €¦etc Sep Passion 1 0 0 1 0 Mango 2 0 0 1 0 Peach 1 0 0 0 0 Acai 1 0 0 0 1 Coconut 1 0 2 0 0 As usual searching gave me so many possibilities that I have many messy solutions. I have a super long nested formula. It has "CountIF, Month, IF, wildcard The main problem I am facing is getting any formula I write to only count within a particular month. Here is where I am so far€¦ =IF(MONTH(B2)=1,COUNTIF(A2:A12,"Passion*"),0) Solution (incorrect): 2 (for January) Thanks for your assitance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Finesse Needed: COUNTIF or DCOUNT or Something better
OK, make sure your months are actual numbers.. Jan would be, ay, 1/1
formatted as mmm. in A2, =SUMPRODUCT(--(ISERROR(SEARCH($A2,Sheet2!$A$2:$A$500))=FALSE),--(month(Sheet2!$B$2:$B$500)=month(B$1))) should get it "TechieGirl" wrote: This is a basic representation for what the data I have to classify/valdate looks like. Flavor Date Passion Peach 1/1/2009 Mango Brandy 1/1/2009 Mango 1/2/2009 Peach 1/3/2009 Acai 1/8/2009 Coconut Rum 1/19/2009 Coconut 3/7/2009 Coconut Rum 3/21/2009 Mango Brandy 7/14/2009 Passion Fruit 7/14/2009 Acai Nut 9/9/2009 I am trying to find the tightest way to show during a 1 month period how much of each flavor was purchased. Only the primary flavor counts. I am hoping to get my data to look like this: Flavor Jan Feb Mar €¦etc Sep Passion 1 0 0 1 0 Mango 2 0 0 1 0 Peach 1 0 0 0 0 Acai 1 0 0 0 1 Coconut 1 0 2 0 0 As usual searching gave me so many possibilities that I have many messy solutions. I have a super long nested formula. It has "CountIF, Month, IF, wildcard The main problem I am facing is getting any formula I write to only count within a particular month. Here is where I am so far€¦ =IF(MONTH(B2)=1,COUNTIF(A2:A12,"Passion*"),0) Solution (incorrect): 2 (for January) Thanks for your assitance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Finesse Needed: COUNTIF or DCOUNT or Something better
Hi Sean,
Thanks so much for your response. Can you tell me more about what your solution is doing? I would like to understand the answer for future use. Sonya "Sean Timmons" wrote: OK, make sure your months are actual numbers.. Jan would be, ay, 1/1 formatted as mmm. in A2, =SUMPRODUCT(--(ISERROR(SEARCH($A2,Sheet2!$A$2:$A$500))=FALSE),--(month(Sheet2!$B$2:$B$500)=month(B$1))) should get it "TechieGirl" wrote: This is a basic representation for what the data I have to classify/valdate looks like. Flavor Date Passion Peach 1/1/2009 Mango Brandy 1/1/2009 Mango 1/2/2009 Peach 1/3/2009 Acai 1/8/2009 Coconut Rum 1/19/2009 Coconut 3/7/2009 Coconut Rum 3/21/2009 Mango Brandy 7/14/2009 Passion Fruit 7/14/2009 Acai Nut 9/9/2009 I am trying to find the tightest way to show during a 1 month period how much of each flavor was purchased. Only the primary flavor counts. I am hoping to get my data to look like this: Flavor Jan Feb Mar €¦etc Sep Passion 1 0 0 1 0 Mango 2 0 0 1 0 Peach 1 0 0 0 0 Acai 1 0 0 0 1 Coconut 1 0 2 0 0 As usual searching gave me so many possibilities that I have many messy solutions. I have a super long nested formula. It has "CountIF, Month, IF, wildcard The main problem I am facing is getting any formula I write to only count within a particular month. Here is where I am so far€¦ =IF(MONTH(B2)=1,COUNTIF(A2:A12,"Passion*"),0) Solution (incorrect): 2 (for January) Thanks for your assitance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF? DCOUNT? | Excel Worksheet Functions | |||
use of DCOUNT formula | Excel Discussion (Misc queries) | |||
countif formula needed | Excel Discussion (Misc queries) | |||
DCOUNT Help Needed | Excel Worksheet Functions | |||
if statement" desperate insight is needed!!!!!! IF,Count, DCOUNT AHHHGGGGGGG! | Excel Worksheet Functions |