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

#### iansilv

##### New Member
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.

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Have you tried a Pivot Table?

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

iansilv said:
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?

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.

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.

iansilv said:
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.

Replies
7
Views
230
Replies
7
Views
93
Replies
4
Views
312
Replies
1
Views
1K
Replies
7
Views
301

1,211,434
Messages
6,101,836
Members
447,758
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back