Four colums, one with a date and three variables. Help?

[iansilv]

Hi-
I have an exported excel file from filemaker pro. it has the following columns of data:
Column 1. Date (current all the way back to 2004, in mm/dd/yyyy format)
Column 2. Variable 1: rejected, retained, open, referred
Column 3. Variable 3: AA, BB, CC, DD
Column 4. Variable 4: red, blue, green, yellow.

I want a formula to calculate the following- how many times during a month- October for example- a row contains 3 specific varialbes- rejected, AA, red for example. I would like to setup this report to do the totals by a month, and be able to update the source spreadsheet from filemaker pro every month. Is this some variation of sumproduct? Any help would be very aprpeciated Thank you.

 

[Andrew Poulsom]

Have you tried a Pivot Table?

 

[iansilv]

What is a pivot table? Can I use sumproduct and do a few instances of values multiplied together?

 

[Brian from Maui]

What is a pivot table? Can I use sumproduct and do a few instances of values multiplied together?

When the file is exported, are the dates true date values? Does the year matter, that is all occurrences for Oct 2004 and 2005 or just 2004?

 

[iansilv]

yes- the year does matter- the records bo back for 1 1/2 years. Thank you for the repsonse! I am tryign to make a spreadsheet that will jsut read the exported file and computer values in a report.

 

[Legacy 51064]

If your months are in column A1 on sheet1(starting with Jan)

in B1:

=SUMPRODUCT((Sheet!2A&2:A12>=1/31/04)*(Sheet!2A&2:A12<=1/1/04)*(Sheet!2B&2:B12="rejected")*(Sheet!2C&2:C12="AA")*(Sheet!2D&2:D12="red"))

You can adjust the variables manually or have them reference fixed cells.

 

[Brian from Maui]

Hi-
I have an exported excel file from filemaker pro. it has the following columns of data:
Column 1. Date (current all the way back to 2004, in mm/dd/yyyy format)
Column 2. Variable 1: rejected, retained, open, referred
Column 3. Variable 3: AA, BB, CC, DD
Column 4. Variable 4: red, blue, green, yellow.

I want a formula to calculate the following- how many times during a month- October for example- a row contains 3 specific varialbes- rejected, AA, red for example. I would like to setup this report to do the totals by a month, and be able to update the source spreadsheet from filemaker pro every month. Is this some variation of sumproduct? Any help would be very aprpeciated Thank you.

On the assumption that the date column house true date values and a test for year and month, try,

=SUMPRODUCT(--(A1:A10-DAY(A1:A10)+1=F1),--(B1:B10="Rejected"),--(C1:C10="AA"),--(D1:D10="Red"))

Where F1 houses the first day of the month and year of interest.