Re-post:Formula Finesse Needed: COUNTIF or DCOUNT or Something bet

Hello,

I might have posted my original question in the wrong area initially. The
answer i recieved from the General Excel forum was way beyond my ken.

Please see the post below and the answer I recieved. If someone could
explain how the answer works or send an alternative answer that would be
great.


"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.

In the table you create to display your results, make sure your headers are
entered as dates, such as 1/1/09, 2/1/09, etc. You can then format the cell
to only display an abbreviated month name (like in your example) using a
custom format of:
mmm

For purpose of this formula, I'll assume your data is on Sheet1, and you're
building your new results table on Sheet2.

Again, to make sure setup is correct, your list of flavors is starting in
A2. Formula in B2 then should be:

=SUMPRODUCT(--(MONTH('Sheet1'!$B$2:$B$200)=MONTH(B$1)),--('Sheet1'!$A$2:$A$200=$A2))

Adjust sheet names and ranges as needed. Note that the ranges need to be the
same size. You should then be able to copy this formula down and across to
fill the rest of your table.

Formula works by creating 2 sets of true/false arrays, and then multiplying
them. Only when you have a True - True combination does the formula give a
value of 1, which is then added to running total.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TechieGirl" wrote:

Hello,

I might have posted my original question in the wrong area initially. The
answer i recieved from the General Excel forum was way beyond my ken.

Please see the post below and the answer I recieved. If someone could
explain how the answer works or send an alternative answer that would be
great.


"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.

This is perfect. I did not want to use the answer without understanding it.
I marked both posts as answered since it works perfectly.

"Luke M" wrote:

In the table you create to display your results, make sure your headers are
entered as dates, such as 1/1/09, 2/1/09, etc. You can then format the cell
to only display an abbreviated month name (like in your example) using a
custom format of:
mmm

For purpose of this formula, I'll assume your data is on Sheet1, and you're
building your new results table on Sheet2.

Again, to make sure setup is correct, your list of flavors is starting in
A2. Formula in B2 then should be:

=SUMPRODUCT(--(MONTH('Sheet1'!$B$2:$B$200)=MONTH(B$1)),--('Sheet1'!$A$2:$A$200=$A2))

Adjust sheet names and ranges as needed. Note that the ranges need to be the
same size. You should then be able to copy this formula down and across to
fill the rest of your table.

Formula works by creating 2 sets of true/false arrays, and then multiplying
them. Only when you have a True - True combination does the formula give a
value of 1, which is then added to running total.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TechieGirl" wrote:

Hello,

I might have posted my original question in the wrong area initially. The
answer i recieved from the General Excel forum was way beyond my ken.

Please see the post below and the answer I recieved. If someone could
explain how the answer works or send an alternative answer that would be
great.


"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.