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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TechieGirl - 30 Sep 2009 17:32 GMT

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.

Reply to this Message

Luke M - 30 Sep 2009 19:56 GMT

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.

Signature

Best Regards,

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

> Hello,
>
[quoted text clipped - 58 lines]
> > > Solution (incorrect): 2 (for January)
> > > Thanks for your assitance.

Reply to this Message

TechieGirl - 30 Sep 2009 20:20 GMT

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

> 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
[quoted text clipped - 80 lines]
> > > > Solution (incorrect): 2 (for January)
> > > > Thanks for your assitance.

Reply to this Message