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

[TechieGirl]

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.

 

[Luke M]

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.

 

[TechieGirl]

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