Help me with a formula please...

J

Jon

I need to create a formula that counts the number of cells that contain a keyword in one column and then further filters this down by month/year data from another column. Is there anyone out there who might be able to help me, if this is at all possible? Thanks!!!!
 
E

Earl Kiosterud

Jon,

I'm not sure I understand your question, but I think you might want a pivot
table. It can be set to give you counts.


--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Jon said:
I need to create a formula that counts the number of cells that contain a
Click to expand...
keyword in one column and then further filters this down by month/year data
from another column. Is there anyone out there who might be able to help
me, if this is at all possible? Thanks!!!!!
 
J

JMay

If A1:B6 is:

ABC 1/31/03
XYZ 4/5/03
BBB 6/30/03
XYZ 4/30/03
XYZ 4/15/03
CCC 12/31/03

In D1 enter "XYZ" In E1 enter "4" In F1 enter "2003" <
Your selection here without the quotes..
In G1 enter:

=SUMPRODUCT((A1:A6=$D$1)*((MONTH(B1:B6)=$E$1))*((YEAR(B1:B6)=$F$1)))



Jon said:
I need to create a formula that counts the number of cells that contain a
Click to expand...
keyword in one column and then further filters this down by month/year data
from another column. Is there anyone out there who might be able to help
me, if this is at all possible? Thanks!!!!!
 
D

Dana DeLouis

Hello. I would recommend a pivot table of your data. Assume you have
heading for your data named "Words" and "Date." Make a Pivot Table and drag
"Words" and "Date" to the Row area of the Row area of the Pivot Table
Wizard. When you are finished, and displaying your Pivot Table, Right Click
one of your Date items in the Pivot Table. Select "Group and Show Detail,"
then select "Group."

Here, select both Year and Month at the same time. Rearrange your data to
what you want when finished by dragging the field names.

A nice thing to do is to go to "Pivot Table Options" and uncheck "Save data
with Table layout." This way, you will not be duplicating your data when
you close the workbook.
HTH.
--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


Jon said:
I need to create a formula that counts the number of cells that contain a
Click to expand...
keyword in one column and then further filters this down by month/year data
from another column. Is there anyone out there who might be able to help
me, if this is at all possible? Thanks!!!!!
 
Top