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

iansilv

New Member
Joined
Mar 22, 2004
Messages
24
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 :cry: 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"
What is a pivot table? Can I use sumproduct and do a few instances of values multiplied together?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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 :cry: 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.
 
Upvote 0

Forum statistics

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

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top