SUMIFS incorrect results when adding rows or sorting columns

P

PaulyK

Board Regular
Joined
Aug 27, 2015
Messages
50
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
Hi,

I have an issue with a SUMIFS formula. The formula works fine until I add an additional row (when i have to add a new Person) and when i sort (or filter) any of the columns B or C. If I do sort (for instance A-Z) the formula gets messed up and the result is wrong.

The Formula:
=SUMIFS('Project allocation'!D:D,'Project allocation'!$C:$C,"="&'Time allocation - Days'!$A2)

I need the formula to always be using the Name in Column A.

What am I doing wrong? Do I need to include an INDEX MATCH or Lookup to make it work?

Days Allocated
Resource Allocation Tool - RAT - sample.xlsx
ABCDEFGHIJKLMNO
1NAMETEAMGroupDays for Apr-22Apr-22Days for May 22May-22Days for June 22Jun-22Days for July 22Jul-22Days for Aug 22Aug-22Days for Sep 22Sep-22
2Person 1Delivery 1.00.02.50.02.50.02.50.00.00.0
3Person 2Delivery MLP 9.01.09.014.06.010.09.014.09.00.09.00.0
4Person 3Head of IT 17.50.018.00.05.00.0
5Person 4Delivery BA 17.010.020.020.020.05.020.010.015.00.015.0
6Person 5Head of IT 10.018.510.029.57.024.514.029.018.01.0
7Person 6Governance Gov 0.00.00.00.00.00.0
8Person 7Delivery PM 20.020.020.019.014.014.02.07.010.0
9Person 8Head of IT 10.03.07.53.03.01.00.01.0
10Person 9Delivery PM 19.011.520.010.520.05.020.01.020.00.020.01.5
11Person 10Head of IT 0.00.00.00.00.00.0
12Person 11 BA 20.010.020.025.020.030.020.030.020.025.0
13Person 12 0.00.00.00.00.00.0
14Person 13Data Data 15.00.00.01.00.01.00.01.00.00.00.00.0
15Person 14Delivery 0.57.00.00.00.00.0
16Person 15Delivery 7.07.07.07.07.07.07.07.07.07.07.07.0
17Person 16Security Security 5.04.07.07.57.50.07.5
18Person 17Delivery 14.026.012.06.012.044.012.024.012.029.00.0
19Person 18Head of IT ITBP 20.019.08.019.010.014.01.0
20Person 19Head of IT 4.06.04.01.510.011.015.00.020.019.0
21Person 20Architecture SA 8.06.08.06.06.00.06.00.06.00.08.00.0
22Person 21Data DA 0.02.06.04.04.04.02.00.0
23Person 22Delivery BA 0.01.05.09.55.08.520.03.00.011.0
24Person 23Architecture SA 0.017.010.012.06.011.05.010.00.010.05.0
25Person 24 MLP 19.00.015.015.012.012.022.025.010.010.00.02.0
26Person 25 DA 2.04.04.00.00.00.0
27Person 26Architecture SA 15.022.516.045.516.031.516.031.516.07.516.022.5
Time allocation - Days
Cell Formulas
RangeFormula
B2:B27B2=IFERROR(INDEX(NameTbl[Team],MATCH([@NAME],NameTbl[Name],0))," ")&" "
C2:C27C2=IFERROR(INDEX(NameTbl[Group],MATCH([@NAME],NameTbl[Name],0))," ")&" "
E2:E27E2=SUMIFS('Project allocation'!D:D,'Project allocation'!$C:$C,"="&'Time allocation - Days'!$A2)
G2:G27G2=SUMIFS('Project allocation'!E:E,'Project allocation'!$C:$C,"="&'Time allocation - Days'!$A2)
I2:I27I2=SUMIFS('Project allocation'!F:F,'Project allocation'!$C:$C,"="&'Time allocation - Days'!$A2)
K2:K27K2=SUMIFS('Project allocation'!G:G,'Project allocation'!$C:$C,"="&'Time allocation - Days'!$A2)
M2:M27M2=SUMIFS('Project allocation'!H:H,'Project allocation'!$C:$C,"="&'Time allocation - Days'!$A2)
O2:O27O2=SUMIFS('Project allocation'!I:I,'Project allocation'!$C:$C,"="&'Time allocation - Days'!$A2)
Named Ranges
NameRefers ToCells
Summary!IT_Team=NameTbl[Name]B2:C27
'Summary - Pipeline (Days)'!IT_Team=NameTbl[Name]B2:C27
IT_Team=NameTbl[Name]B2:C27
rngWho=TblProjAlloc[WHO]O2:O27, M2:M27, K2:K27, I2:I27, G2:G27, E2:E27
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D2:O27Expression=ISBLANK(D2)=TRUEtextNO
Cells with Data Validation
CellAllowCriteria
A2:A27List=IT_Team


Project Allocation
Resource Allocation Tool - RAT - sample.xlsx
ABCDEFGHI
1PROJECT NUMBERPROJECT NAMEWHOApr-22May-22Jun-22Jul-22Aug-22Sep-22
2190158Project 1Person 231055555
3200067Project 2Person 50.50.5111
4200067Project 2Person 140.57
5200067Project 2Person 267777
6210001Project 3Person 1888888
7210004Project 4Person 24362
8210007Project 5Person 214
9210007Project 5Person 92.55.511.5
10210008Project 6Person 51111
11210008Project 6Person 1754222
12210020Project 7Person 16477.57.57.5
13210022Project 8Person 261207.57.57.5
14210024Project 9Person 26827.57.57.5
15210062Project 10Person 111520202020
16210070Project 11Person 599666
17210070Project 11Person 833311
18210070Project 11Person 260.50.50.50.50.50.5
19210071Project 12Person 581917.52111
20210074Project 13Person 9555
21210099Project 14Person 172
22210115Project 15Person 1811211
23210115Project 15Person 227.57.5111
24210116Project 16Person 1810105
25210136Project 17Person 17202020
26210138Project 18Person 94
27210138Project 18Person 11101010105
28210138Project 18Person 234545
29210141Project 19Person 317.5185
30210141Project 19Person 1712185
31210143Project 20Person 233225
32210147Project 21Person 15777777
33210148Project 22Person 1772422
34210158Project 23Person 1961.5111519
35210163Project 24Person 2066
36210163Project 24Person 266161616
37210167Project 25Person 221212
38210168Project 26Person 25244
39210183Project 27Person 4101010
40210183Project 27Person 77757
41210184Project 28Person 77655
42210185Project 29Person 4101055
43210185Project 29Person 766425
44210186Project 30Person 13111
45210186Project 30Person 212442
46220006Project 31Person 2101014
47220006Project 31Person 241512224
Project allocation
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C2:C47Expression=COUNTBLANK(C2)textNO
A2:C49Expression=$B2="Awaiting PPM Upload"textNO
D2:I47Expression=COUNTBLANK(D2)textNO
Cells with Data Validation
CellAllowCriteria
C2:C47List=IT_Team
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Remove the name of the sheet the formula is in from the formula.
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
How to count a cell that looks Blank as Blank despite it containing formula
Replies
3
Views
545
Fluff
Fluff
P
  • Question Question
Summarise result of Sumifs using different criteria
Replies
3
Views
521
PaulyK
P
V
  • Solved
sum table based on rows and multiple years columns
Replies
2
Views
513
vampsthevampyre
V
P
  • Question Question
Combining two data tables in Sumifs Project Summary Report
Replies
1
Views
444
PaulyK
P
P
  • Solved
Sum by Name, Project and Date (in column)
2 3
Replies
20
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top