Formula to add additional names to Sumifs list?

Coyotex3

Well-known Member
Joined
Dec 12, 2021
Messages
507
Office Version
  1. 365
Platform
  1. Windows
Hello guys, so I have a sheet with the following datasets:

PERSONAL.xlsm
ABCDEFGH
Sheet3
PERSONAL.xlsm
ABCDEFGHI
1ContributorCustomerAmountCustomerProfitIndividual 1Individual 2Individual 3
2Individual 1Client A100Client A930100130450
3Individual 2Client B200Client B19501502001500
4Individual 3Client C300Client C650250100300
5Individual 4Client A250
6Individual 1Client B150
7Individual 2Client C100
8Individual 3Client A450
9Individual 4Client B100
10Individual 1Client C250
11Individual 2Client A130
12Individual 3Client B1500
Sheet3
Cell Formulas
RangeFormula
F2:F4F2=SUMIFS(Table1[Amount],Table1[Customer],E2)
G2:I2,H3:I4G2=SUMIFS(Table1[Amount],Table1[Customer],$E2,Table1[Contributor],G$1)
G3:G4G3=SUMIFS(Table1[Amount],Table1[Customer],E3,Table1[Contributor],G$1)
The dataset on the left is updated daily and I would like to be able to dynamically update the dataset with the formula to account for any new clustomers which Individuals 1-3 worked with?
PERSONAL.xlsm
ABCDEFGHI
1ContributorCustomerAmountCustomerProfitIndividual 1Individual 2Individual 3
2Individual 1Client A100Client A1030100130450
3Individual 2Client B200Client B23001502001500
4Individual 3Client C300Client C773250100300
5Individual 4Client A250Client D10002000300
6Individual 1Client B150Client E75002500
7Individual 2Client C100
8Individual 3Client A450
9Individual 4Client B100
10Individual 1Client C250
11Individual 2Client A130
12Individual 3Client B1500
13Individual 4Client C123
14Individual 5Client A100
15Individual 6Client E500
16Individual 7Client D400
17Individual 5Client B350
18Individual 6Client F200
19Individual 7Client G250
20Individual 5Client F350
21Individual 6Client G205
22Individual 7Client D100
23Individual 1Client D200
24Individual 2Client E250
25Individual 3Client D300
Sheet3
Cell Formulas
RangeFormula
F2:F6F2=SUMIFS(Table1[Amount],Table1[Customer],E2)
G2:I2,H3:I6G2=SUMIFS(Table1[Amount],Table1[Customer],$E2,Table1[Contributor],G$1)
G3:G6G3=SUMIFS(Table1[Amount],Table1[Customer],E3,Table1[Contributor],G$1)
Lastly, if possible, would there be a way of seeing if any outside contributor(4-7) worked with Customer(A-C) on a separate list?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,812
Messages
6,181,104
Members
453,021
Latest member
Justyna P

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