Solved

Sumproduct question with a twist...

Posted on 2011-02-11
4
255 Views
Last Modified: 2013-11-05
I need to Sum the "Actual" and "Budget" data from "Building" on distinct "Unit" supplying the Period" and "Building" for the formula.

Can anyone help?

NG,
screenshot.16.jpg
0
Comment
Question by:nike_golf
  • 2
  • 2
4 Comments
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 500 total points
ID: 34873658

Nike,

I can think about 3 possible views to do this...

A. Either Build a Pivot for it and then instead of SUM Use Average and first group by  Building and then unit since all your values are same you will get the right result as in 1 record only.

B. In Column-G2 Use this formula...and drag this till bottom...
=if(sumproduct((C$1:C2=C2)*(D$1:D2=D2))=1,1,0)

Then use this Column to get your values where the value is equal to 1.. as you just filter for unique records..

C. Write a user defined function to do what you are looking for...

Saurabh...
0
 
LVL 13

Author Comment

by:nike_golf
ID: 34873849
Do you have anything for a custom function that you could start me off with?
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 34873895

I don't have one right now, However if you are intrested can build one for you..if you can use it..let me know..so that can start building one for you...

Also is it possible for you to give the excel file as it will become easy for me to stimulate your data,...
0
 
LVL 13

Author Comment

by:nike_golf
ID: 34873908
I might try running with item 2 you suggested I'll post back... thanks.

NG,
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel In-Cell Dropdown is very wide 5 40
Excel Formula 5 43
Excel Cell Total 3 20
Select Next Route by Time 4 19
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question