Solved

Sumproduct question with a twist...

Posted on 2011-02-11
4
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

632 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