Solved

Calculation not summing correctly

Posted on 2013-11-04
4
223 Views
Last Modified: 2013-11-06
Hi not sure how to update my calculation so that it is pulling the correct sum.  Looking at the attached workbook, I am trying to sum the reason codes that are the "OnTime" tab on the "OnTimeCals" tab.  Column Y (LLD-POSS) has the formula that I am using to sum the numbers, however it not summing based on the store number in column A.  What would be best way to get the correct sum?
RCSummary-EE04.xlsx
0
Comment
Question by:jmac001
[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
4 Comments
 
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 100 total points
ID: 39623067
From what I see from your formula, you have run the formula twice. Means adding Columns Q, S, W, AA, AE, AM, and AQ twice.

Is what you are looking for 135? Or is that you haven't included all the columns, and you are looking for 267?

Since I'm not sure if you left out Column AJ on purpose, I have created a duplicate tab that includes the Column AJ.
RCSummary-EE04.xlsx
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 400 total points
ID: 39623107
It's difficult to decipher what your current formula is doing - you are using SUMIF functions but only summing for a single row (and using superfluous multi-column references).

Perhaps try this formula in Y2 copied down

=SUMPRODUCT(ISSVAR[[PCR Approval- Site Info Actual]:[Construction End - Open Variance]],ISNUMBER(SEARCH("L*POSS",ISSVAR[[PCR Approval- Site Info Receipt Reason Code]:[Construction End - Open Reason Code]]))*(ISSVAR[Store '#]=OnTimeCals!A2))

That sums the variances where the reason code is "L*POSS" and the store number matches too (although if you only have one row per store in your real data it's debatable whether you need to look at multiple rows).

My formula gives the same results as Harry;s "with AJ" option.....

regards, barry
0
 

Author Comment

by:jmac001
ID: 39624292
AJ was left out intentionally (deciding if this task is going to be measured).  I started to second guess myself I have another calculation and the two numbers were not matching.  Barry I like your formula because it is more is more managable then the formula I have been using.  Barry you included [[PCR Approval- Site Info Actual]:[Construction End - Open Variance]] this is considered my "Pre-Start" info and I want to look at the "Start" info only.  So is that a matter of just changing  to [Site Info Receipt - SD Start Variance]?  

Attaching the latest file with the other the tab RC codes which is calculation that I referenced above.  Calculating this so that I can create a chart off of the pivot and a check to make sure the numbers are correct.
RCSummary-EE04-v2.xlsx
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 400 total points
ID: 39624582
My suggestion simplifies the formula because it's the equivalent of replacing

=SUMIF(A:A,"x",B:B)+SUMIF(C:C,"x",D:D)+SUMIF(E:E,"x",F:F)+SUMIF(G:G,"x",H:H)

with a single SUMIF like

=SUMIF(A:G,"x",B:H)

In your case because you have another condition that uses a single column you can't do that within SUMIF in conjunction with a multi-column condition.....but you can do that in SUMPRODUCT. If you want to change the ranges that's fine but note that you need to keep the first two ranges equal in size, as are

ISSVAR[[PCR Approval- Site Info Actual]:[Construction End - Open Variance]]

and

ISSVAR[[PCR Approval- Site Info Receipt Reason Code]:[Construction End - Open Reason Code]]

If that means including AJ which you don't want then you can always subtract a SUMIF to remove that value

regards, barry
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

751 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