Need help on a formula Maybe SumIfs

Z

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,125
Office Version
  1. 365
Platform
  1. Windows
So i made an example sheet below. I need the total gross from Columns E from the ID's from Column A ,Total Gross by different class codes.

You can see if i am thinking right maybe the formula goes in Column H. If i pull the formula down in Column H to fill in Columns F ,G, H. You can see F has id number 5 3x, While G has 3 different class codes. Sometimes it can be 4 or 5. and H with the totals of ID 5 by class code in H. The reason why I'm looking for something like this is because my sheet goes down to line 3000 lines of data. So i am going through each ID number for totals and different class codes. Taking me forever. Any help would be great as this Formula is very complicated for me. Employee ID Starts at Column A.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
1​
[/td][td]
employee_ID​
[/td][td]
first_name​
[/td][td]
last_name​
[/td][td]
class_code​
[/td][td]
gross_employee_pay ​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Results[/td][td]Results[/td][td] Formula [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
3​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Yard[/td][td]
4280.00​
[/td][td]
5​
[/td][td]Yard[/td][td]
$ 25,780.00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
4​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Supervisor[/td][td]
4280.00​
[/td][td]
5​
[/td][td]Supervisor[/td][td]
$ 31,840.50​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
5​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Supervisor[/td][td]
4761.50​
[/td][td]
5​
[/td][td]Concrete[/td][td]
$ 15,876.00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
6​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Supervisor[/td][td]
5243.00​
[/td][td]
9​
[/td][td]Yard[/td][td]
$ 8,050.00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
7​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Yard[/td][td]
4280.00​
[/td][td]
9​
[/td][td]Supervisor[/td][td]
$ 9,822.00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
8​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Yard[/td][td]
6244.00​
[/td][td]
9​
[/td][td]Concrete[/td][td]
$ 3,560.00​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
9​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Supervisor[/td][td]
6328.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
10​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Yard[/td][td]
5992.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
11​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Supervisor[/td][td]
5656.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
12​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Yard[/td][td]
4984.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
13​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Concrete[/td][td]
4816.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
14​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Concrete[/td][td]
5656.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
15​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Concrete[/td][td]
5404.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
16​
[/td][td]
5​
[/td][td]Joe[/td][td]S[/td][td]Supervisor[/td][td]
5572.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
17​
[/td][td]
9​
[/td][td]Alex[/td][td]S[/td][td]Yard[/td][td]
2350.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
18​
[/td][td]
9​
[/td][td]Alex[/td][td]S[/td][td]Concrete[/td][td]
3560.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
19​
[/td][td]
9​
[/td][td]Alex[/td][td]S[/td][td]Supervisor[/td][td]
4250.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
20​
[/td][td]
9​
[/td][td]Alex[/td][td]S[/td][td]Yard[/td][td]
2350.00​
[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]
21​
[/td][td]
9​
[/td][td]Alex[/td][td]S[/td][td]Yard[/td][td]
3350.00​
[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Try:

Book1
ABCDEFGH
10
2employee_IDfirst_namelast_nameclass_codegross_employee_pay
3ResultsResultsFormula
45JoeSYard4280.005Yard25780
55JoeSSupervisor4280.005Supervisor31840.5
65JoeSSupervisor4761.505Concrete15876
75JoeSSupervisor5243.009Yard8050
85JoeSYard4280.009Concrete3560
95JoeSYard6244.009Supervisor4250
105JoeSSupervisor6328.00
115JoeSYard5992.00
125JoeSSupervisor5656.00
135JoeSYard4984.00
145JoeSConcrete4816.00
155JoeSConcrete5656.00
165JoeSConcrete5404.00
175JoeSSupervisor5572.00
189AlexSYard2350.00
199AlexSConcrete3560.00
209AlexSSupervisor4250.00
219AlexSYard2350.00
229AlexSYard3350.00
23
Sheet1
Cell Formulas
RangeFormula
F4:H9F4=LET(a,CHOOSE({1,2},A4:A22,D4:D22),b,UNIQUE(a),c,INDEX(b,0,1),d,INDEX(b,0,2),CHOOSE({1,2,3},c,d,SUMIFS(E4:E22,A4:A22,c,D4:D22,d)))
Dynamic array formulas.
 
Upvote 0
Solution
Eric W said:
Try:
Click to expand...
Works Thank you

Eric W said:
Eric W said:
Try:

Book1
ABCDEFGH
10
2employee_IDfirst_namelast_nameclass_codegross_employee_pay
3ResultsResultsFormula
45JoeSYard4280.005Yard25780
55JoeSSupervisor4280.005Supervisor31840.5
65JoeSSupervisor4761.505Concrete15876
75JoeSSupervisor5243.009Yard8050
85JoeSYard4280.009Concrete3560
95JoeSYard6244.009Supervisor4250
105JoeSSupervisor6328.00
115JoeSYard5992.00
125JoeSSupervisor5656.00
135JoeSYard4984.00
145JoeSConcrete4816.00
155JoeSConcrete5656.00
165JoeSConcrete5404.00
175JoeSSupervisor5572.00
189AlexSYard2350.00
199AlexSConcrete3560.00
209AlexSSupervisor4250.00
219AlexSYard2350.00
229AlexSYard3350.00
23
Sheet1
Cell Formulas
RangeFormula
F4:H9F4=LET(a,CHOOSE({1,2},A4:A22,D4:D22),b,UNIQUE(a),c,INDEX(b,0,1),d,INDEX(b,0,2),CHOOSE({1,2,3},c,d,SUMIFS(E4:E22,A4:A22,c,D4:D22,d)))
Dynamic array formulas.
Click to expand...


Book1
ABCDEFGH
10
2employee_IDfirst_namelast_nameclass_codegross_employee_pay
3ResultsResultsFormula
45JoeSYard4280.005Yard25780
55JoeSSupervisor4280.005Supervisor31840.5
65JoeSSupervisor4761.505Concrete15876
75JoeSSupervisor5243.009Yard8050
85JoeSYard4280.009Concrete3560
95JoeSYard6244.009Supervisor4250
105JoeSSupervisor6328.00
115JoeSYard5992.00
125JoeSSupervisor5656.00
135JoeSYard4984.00
145JoeSConcrete4816.00
155JoeSConcrete5656.00
165JoeSConcrete5404.00
175JoeSSupervisor5572.00
189AlexSYard2350.00
199AlexSConcrete3560.00
209AlexSSupervisor4250.00
219AlexSYard2350.00
229AlexSYard3350.00
23
Sheet1
Cell Formulas
RangeFormula
F4:H9F4=LET(a,CHOOSE({1,2},A4:A22,D4:D22),b,UNIQUE(a),c,INDEX(b,0,1),d,INDEX(b,0,2),CHOOSE({1,2,3},c,d,SUMIFS(E4:E22,A4:A22,c,D4:D22,d)))
Dynamic array formulas.
Click to expand...
 
Upvote 0
I been using this formula for 2 years now and its the best for me but i need to update it. I need formula to read another column also with different jobs in it.
Lets say i insert column F to move that over right. Now column F has different jobs in it. You see how Column G now has different class codes and gives you the Gross per ID in Column H. Now I also need it to give me the same but with different jobs and class codes per ID #. I can give you an example tomorrow and maybe you can understand more. Let me know thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
Updating a formula =Min but Sum up first
2
Replies
10
Views
83
Fluff
Fluff
DNapoli
  • Solved
Index and Match Excel Question.
2 3
Replies
24
Views
1K
Peter_SSs
Peter_SSs
Z
  • Question Question
Formula help Need SUMIFS for a column with starting numbers
Replies
3
Views
108
zone709
Z
U
  • Solved
Index or SumIf on here..
Replies
9
Views
298
Cubist
Cubist
S
  • Question Question
Macro for
Replies
2
Views
65
Sanderb
S

Forum statistics

Threads
1,223,262
Messages
6,171,080
Members
452,377
Latest member
bradfordsam

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