Solved

Calculation not summing correctly

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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