Sum of Amount of salesperson brandwise and non brand wise

Hello Excel Family, I am trying to solve this question from last 2 hours and didn't get the accurate answer, i tried using sumproduct, sumifs, vlookup but didn't get the desired answer, Please help t...

BI & Data Analysis

developer

Formulas and Functions

Macros and VBA

power bi

OliverScheurich
Dec 16, 2022
ishaqib
=IFNA(IF(MATCH(D5&F5,$A$5:$A$8&$B$5:$B$8,FALSE),1),0)
A possible solution could be with a helper formula in cell G5. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021. Then fill the formula down.

In cell G20:
=SUMPRODUCT((F20=$D$5:$D$10)*$E$5:$E$10*$G$5:$G$10)
In cell H20:
=SUMPRODUCT((F20=$D$5:$D$10)*$E$5:$E$10*NOT($G$5:$G$10))

PeterBartholomew1

Silver Contributor

Dec 16, 2022

ishaqib

A solution that works only with Excel 365

= LET(
    p,       INDEX(person,n),
    groups,  FILTER(BrandTbl[Group],  BrandTbl[SalesPerson] = p),
    totals,  SUMIFS(SalesTbl[Amount], SalesTbl[Person],  p),
    amounts, SUMIFS(SalesTbl[Amount], SalesTbl[Person],  p, SalesTbl[Group], groups),
    Choose(option, SUM(amounts), totals - SUM(amounts), totals)
  )

ishaqib
Copper Contributor
Dec 27, 2022
Thanks a ton for your reply sir

Forum Discussion

Sum of Amount of salesperson brandwise and non brand wise

Resources