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.

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.

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.