Solved

Calculation not summing correctly

Posted on 2013-11-04
4
212 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
  • 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

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 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

18 Experts available now in Live!

Get 1:1 Help Now