Home

Help with a sumif formula

%3CLINGO-SUB%20id%3D%22lingo-sub-739294%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20sumif%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739294%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20looking%20to%20make%20a%20budget%20spreadsheet%20that%20lets%20me%20see%20when%20money%20has%20been%20spent%20but%20is%20also%20committed.%20I%20have%20managed%20to%20get%20a%20Sumif%20column%20to%20work%20out%20if%20money%20has%20been%20allocated%20within%20a%20certain%20time%20but%20I%20would%20like%20it%20to%20do%20more%20if%20possible.%20I%20cannot%20seem%20to%20get%20a%20formula%20that%20works%20so%20any%20advice%20would%20be%20welcome.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20I%20want%20it%20to%20do%20two%20things%20in%20the%20committed%20spend%20box%3A%3C%2FP%3E%3CP%3E1-%20check%20if%20the%20money%20has%20been%20committed%20within%20the%20year%20e.g.%201%2F04%2F19%20-31%2F03%2F20%2C%3C%2FP%3E%3CP%3E2-%26nbsp%3B%20then%20check%20if%20it%20has%20been%20paid%20out%20by%20looking%20at%20the%20paid%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-739294%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esumif%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-739488%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20sumif%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-739488%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F139501%22%20target%3D%22_blank%22%3E%40Gemma%20Telfer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EYou%20have%20some%20issues%20with%20the%20SUMIFS%20functions%20when%20using%20a%20comparison%20operator.%3C%2FP%3E%3CP%3EYou%20need%20to%3A%3C%2FP%3E%3COL%3E%3CLI%3EInclude%20the%20comparison%20Operator%20in%20Double%20quotations%3C%2FLI%3E%3CLI%3EAdd%20a%20Joining%20operator%20between%20it%20and%20the%20date%20%22%26amp%3B%22%3C%2FLI%3E%3CLI%3ENo%20double%20quotations%20around%20the%20date%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eexample%3C%2FP%3E%3CP%3E%3DSUMIFS(%24F%242%3A%24F%2417%2C%24H%242%3A%24H%2417%2C%22%26gt%3B%3D%22%26amp%3B1-3-2019%2C%24H%242%3A%24H%2417%2C%22%26lt%3B%3D%22%26amp%3B31-3-2020)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740433%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20sumif%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740433%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%2C%20I'd%20use%20DATE()%20instead%20of%20constant%20not%20to%20depend%20on%20locale%2C%20like%3C%2FP%3E%0A%3CPRE%3EDATE(2019%2C3%2C1)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-740457%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20sumif%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-740457%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F139501%22%20target%3D%22_blank%22%3E%40Gemma%20Telfer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%20I%20agree%20with%20the%20final%20touch%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20%3A)%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20just%20focusing%20on%20the%20use%20of%20comparison%20operators%20in%20the%20SUMIFS%20function.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3Ethank%20you%20my%20friend!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Gemma Telfer
Occasional Contributor

Hi 

I am looking to make a budget spreadsheet that lets me see when money has been spent but is also committed. I have managed to get a Sumif column to work out if money has been allocated within a certain time but I would like it to do more if possible. I cannot seem to get a formula that works so any advice would be welcome. 

 

Basically, I want it to do two things in the committed spend box:

1- check if the money has been committed within the year e.g. 1/04/19 -31/03/20,

2-  then check if it has been paid out by looking at the paid column.

3 Replies

@Gemma Telfer 

Hi

You have some issues with the SUMIFS functions when using a comparison operator.

You need to:

  1. Include the comparison Operator in Double quotations
  2. Add a Joining operator between it and the date "&"
  3. No double quotations around the date

 

example

=SUMIFS($F$2:$F$17,$H$2:$H$17,">="&1-3-2019,$H$2:$H$17,"<="&31-3-2020)

 

Hope that helps

Nabil Mourad

@nabilmourad , I'd use DATE() instead of constant not to depend on locale, like

DATE(2019,3,1)

@Gemma Telfer 

Of course I agree with the final touch of @Sergei Baklan :) 

I was just focusing on the use of comparison operators in the SUMIFS function.


thank you my friend!

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