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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

iansilv

New Member
Joined
Mar 22, 2004
Messages
24
What is a pivot table? Can I use sumproduct and do a few instances of values multiplied together?
 

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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?
 

iansilv

New Member
Joined
Mar 22, 2004
Messages
24

ADVERTISEMENT

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.
 
L

Legacy 51064

Guest
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

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,446
Messages
5,642,198
Members
417,259
Latest member
gtacw

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
Top