Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
rev rev is offline
external usenet poster
 
Posts: 1
Default Populate a spreadsheet from a data set

I have a data set from access that I exported into Excel. Now I am trying to
write a formula that will summarize the data based 3 criterias
(appropriation, business org, and expenditure code. So, I want to summarize
all salary data (expenditure code ccs) for appropriation 101, and business
org 11. I have tried an array formula, but I haven't got it to work? Any
suggestions?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,696
Default Populate a spreadsheet from a data set

Pivot Table would do that for you.
Appropriation on your rows, Business org on columns and expenditures in your
data.

If you really don't want to have pivots, Copy and paste special values
elsewhere to ensure all appropriations and biz orgs are captured in rows and
columns as needed, then make a sumproduct:

Assuming columns in the order listed below:
=sumproduct(--(DataSheet!$A$2:$A$5000=$A2),--(DataSheet!$B$2:$B$5000=B$1),(DataSheet!$C$2:$C$50 00=$A2))

where A2 is your first appropriation, B1 is your first biz org.

HTH!


"rev" wrote:

I have a data set from access that I exported into Excel. Now I am trying to
write a formula that will summarize the data based 3 criterias
(appropriation, business org, and expenditure code. So, I want to summarize
all salary data (expenditure code ccs) for appropriation 101, and business
org 11. I have tried an array formula, but I haven't got it to work? Any
suggestions?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to populate master spreadsheet from others based on headers [email protected] Excel Discussion (Misc queries) 0 May 1st 08 06:24 PM
Using a current spreadsheet to populate a new one shadesofsisyphus Excel Discussion (Misc queries) 4 August 9th 06 08:04 PM
How do you populate one spreadsheet from multiple worksheets in a. Curious Excel Worksheet Functions 0 May 9th 06 09:50 PM
Populate a spreadsheet with multiple entries from a master jdall Excel Discussion (Misc queries) 1 April 6th 06 12:31 AM
Can I use a spreadsheet to populate an offline calender in excel? jimmykins Excel Discussion (Misc queries) 0 March 16th 05 11:45 PM


All times are GMT +1. The time now is 11:31 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"