Nesting Sumif calculations

  • Thread starter Thread starter Steve Martin
  • Start date Start date
S

Steve Martin

I need to test two criteria in a spreadsheet before adding
a series of numbers. Specifically, I need to add up all
dollar values where the account manager = "ZM" and
probability = "100%".

Example data
Acct Mgr Customer Probibility Value
ZM ABC co. 80% $1000
AD BCE Co. 100% $3000
ZM AGF co. 100% $2000
GQ DEF co. 90% $6000
ZM GHI co. 100% $4000
AD XYZ co. 90% $2000

The number returned should be $6000 (AM=ZN, Value =100%).

Any help with this calculation would be appreciated!!
Thanks
Steve
 
Try sumproduct

=SUMPRODUCT((A2:A7="ZM")*(C2:C7=100%)*D2:D7)

(A2:A7="ZM") equates to 1 if ZM else 0
(C2:C7=100%) equates to 1 if 100% else 0

if ZM and 100% = 1 then it will sum the dollar amount.

Lance
 
Back
Top