SOLVED

Excel Formula - Filter 2 Rows of Data and then Subtotal a 3rd column.

%3CLINGO-SUB%20id%3D%22lingo-sub-2966567%22%20slang%3D%22en-US%22%3EExcel%20Formula%20-%20Filter%202%20Rows%20of%20Data%20and%20then%20Subtotal%20a%203rd%20column.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2966567%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20to%20create%20a%20formula%20that%20will%20filter%202%20Separate%20columns%20in%20the%20same%20bulk%20data%2C%20and%20the%20totals%20the%20amount%20left%20in%20the%203rd%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%203000%20Rows%20of%20data%2C%20Column%20B%20has%20%22Dollar%20Amounts%E2%80%9D%3B%20Column%20D%20has%20%22Names%E2%80%9D%2C%20Column%20E%20has%20the%20%E2%80%9CLost%2C%20Won%2C%20Open%E2%80%9D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%20Help%3F%3C%2FP%3E%3CP%3EI've%20Tried%2C%20not%20successful%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF('Individual%20YTD'!D%3AD%2C%20%22Name%22%2C%20'Individual%20YTD'!E%3AE%2C%20%3ALost%22%2C'Individual%20YTD'!B%3AB)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSubtotal%2C9('Individual%20YTD'!D%3AD%2C%20%22Name%22%2C%20'Individual%20YTD'!E%3AE%2C%20%3ALost%22%2C'Individual%20YTD'!B%3AB)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2966567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2966610%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20Filter%202%20Rows%20of%20Data%20and%20then%20Subtotal%20a%203rd%20column.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2966610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1218205%22%20target%3D%22_blank%22%3E%40NathanDellinger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMPRODUCT((E2%3AE3000%3D%22Lost%22)*(D2%3AD3000%3D%22Name%22)*B2%3AB3000)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2966627%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20-%20Filter%202%20Rows%20of%20Data%20and%20then%20Subtotal%20a%203rd%20column.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2966627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1218205%22%20target%3D%22_blank%22%3E%40NathanDellinger%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIFS('Individual%20YTD'!B%3AB%2C%20'Individual%20YTD'!D%3AD%2C%20%22Name%22%2C%20'Individual%20YTD'!E%3AE%2C%20%22Lost%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESUMIFS%20instead%20of%20SUMIF.%3C%2FLI%3E%0A%3CLI%3EArguments%20in%20different%20order.%3C%2FLI%3E%0A%3CLI%3E%22Lost%22%20instead%20of%20%3ALost%22%3C%2FLI%3E%0A%3C%2FUL%3E%3C%2FLINGO-BODY%3E
New Contributor

I am looking to create a formula that will filter 2 Separate columns in the same bulk data, and the totals the amount left in the 3rd column. 

 

I have 3000 Rows of data, Column B has "Dollar Amounts”; Column D has "Names”, Column E has the “Lost, Won, Open”

 

Thoughts? Help?

I've Tried, not successful

 

=SUMIF('Individual YTD'!D:D, "Name", 'Individual YTD'!E:E, :Lost",'Individual YTD'!B:B)

 

=Subtotal,9('Individual YTD'!D:D, "Name", 'Individual YTD'!E:E, :Lost",'Individual YTD'!B:B)

1 Reply
best response confirmed by NathanDellinger (New Contributor)
Solution

@NathanDellinger 

Try

 

=SUMIFS('Individual YTD'!B:B, 'Individual YTD'!D:D, "Name", 'Individual YTD'!E:E, "Lost")

 

  • SUMIFS instead of SUMIF.
  • Arguments in different order.
  • "Lost" instead of :Lost"