Calculation not summing correctly

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
jmac001Asked:
Who is Participating?
 
barry houdiniConnect With a Mentor Commented:
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
 
Harry LeeConnect With a Mentor Commented:
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
 
jmac001Author Commented:
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
 
barry houdiniConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.