Solved

Sumproduct question with a twist...

Posted on 2011-02-11
4
253 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
Comment Utility

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
Comment Utility
Do you have anything for a custom function that you could start me off with?
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
Comment Utility

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
Comment Utility
I might try running with item 2 you suggested I'll post back... thanks.

NG,
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now