Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Calculation not summing correctly

Posted on 2013-11-04
4
Medium Priority
?
239 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 400 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 1600 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 1600 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

877 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