SOLVED
Home

Excel formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-449195%22%20slang%3D%22en-US%22%3EExcel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449195%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20create%20a%20formula%20that%20will%20sum%20a%20dollar%20amount%20from%20another%20worksheet%20in%20the%20same%20workbook%20while%20filtering%20two%20types%20of%20criteria.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(Table1%5BMRR%5D)%20%2FCOUNTIFS(Table1%5BStatus%5D%2C%20%22red%22%2C%20Table1%5BPrimary%20CSEP%20Identification%5D%2C%22%26lt%3B%26gt%3BContract%20Renewal%20-%20within%203%20months%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sum%20is%20supposed%20to%20be%20the%20total%2C%20the%20status%20identified%20as%20red%20and%20excluding%20Primary%20CSEP%20identification%20of%20contract%20renewal.%20What%20am%20I%20missing%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-449195%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-449237%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449237%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320472%22%20target%3D%22_blank%22%3E%40abundas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20have%20not%20applied%20any%20filtering%20to%20the%20summation%20which%20I%20assume%20to%20be%20the%20point%20of%20the%20exercise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMIFS(Table1%5BMRR%5D)%2C%20Table1%5BStatus%5D%2C%20%22red%22%2C%20Table1%5BPrimary%20CSEP%20Identification%5D%2C%22%26lt%3B%26gt%3BContract%20Renewal%20-%20within%203%20months%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449774%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449774%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply%20and%20help.%20I%20tried%20this%20formula%20during%20this%20process%20and%20again%20with%20your%20comment%2C%20however%20I%20recieve%20an%20error%20back%20stating%20%22You've%20entered%20too%20few%20arguments%20for%20this%20function%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-449976%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449976%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F320472%22%20target%3D%22_blank%22%3E%40abundas%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20assume%20you%20have%20not%20actually%20provided%20the%205%20parameters%20as%20suggested.%3C%2FP%3E%3CP%3ECheck%20carefully%20that%20you%20have%20conformed%20to%20the%20syntax%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMIFS(%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ETable1%5BMRR%5D)%2C%26nbsp%3B%20%7B%3CEM%3Ethe%20range%20to%20be%20conditionally%20summed%3C%2FEM%3E%7D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ETable1%5BStatus%5D%2C%20%26nbsp%3B%20%7B%3CEM%3Ethe%20first%20criterion%20range%3C%2FEM%3E%7D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22red%22%2C%20%26nbsp%3B%20%7B%3CEM%3Ethe%20value%20you%20required%20for%20the%20first%20criterion%3C%2FEM%3E%7D%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3ETable1%5BPrimary%20CSEP%20Identification%5D%2C%20%26nbsp%3B%3CSPAN%3E%7B%3CEM%3Ethe%20second%20criterion%20range%3C%2FEM%3E%7D%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%22%26lt%3B%26gt%3BContract%20Renewal%20-%20within%203%20months%22)%20%26nbsp%3B%3CSPAN%3E%20%7B%3CEM%3Ethe%20value%20you%20required%20for%20the%20second%20criterion%3C%2FEM%3E%7D%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3Ewith%20commas%20separating%20the%20arguments.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-450025%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-450025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20Peter%2C%20I%20have%20tried%20this%20formula%20many%20times%20but%20when%20you%20broke%20it%20out%20like%20that%20I%20re-wrote%20it%2C%20as%20you%20spelled%20out.%20This%20worked!!%20I%20must've%20missed%20a%20comma%20somewhere%20or%20something%20small%20since%20I%20have%20been%20looking%20at%20it%20for%203%20days%20%3A).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVery%20much%20appreciate%20your%20help%20and%20walking%20me%20through%20it!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
abundas
Occasional Contributor

Trying to create a formula that will sum a dollar amount from another worksheet in the same workbook while filtering two types of criteria.

 

=SUM(Table1[MRR]) /COUNTIFS(Table1[Status], "red", Table1[Primary CSEP Identification],"<>Contract Renewal - within 3 months")

 

The sum is supposed to be the total, the status identified as red and excluding Primary CSEP identification of contract renewal. What am I missing?

4 Replies

@abundas 

You have not applied any filtering to the summation which I assume to be the point of the exercise.

 

=SUMIFS(Table1[MRR]), Table1[Status], "red", Table1[Primary CSEP Identification],"<>Contract Renewal - within 3 months")

 

@Peter Bartholomew 

 

Thank you for your reply and help. I tried this formula during this process and again with your comment, however I recieve an error back stating "You've entered too few arguments for this function".

Highlighted
Solution

@abundas 

Then I assume you have not actually provided the 5 parameters as suggested.

Check carefully that you have conformed to the syntax

=SUMIFS(

Table1[MRR]),  {the range to be conditionally summed}

Table1[Status],   {the first criterion range}

"red",   {the value you required for the first criterion}

Table1[Primary CSEP Identification],  {the second criterion range}

"<>Contract Renewal - within 3 months")   {the value you required for the second criterion}

with commas separating the arguments.

@Peter Bartholomew 

 

Thank you Peter, I have tried this formula many times but when you broke it out like that I re-wrote it, as you spelled out. This worked!! I must've missed a comma somewhere or something small since I have been looking at it for 3 days :).

 

Very much appreciate your help and walking me through it!

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies