SOLVED

Need help with a SumIF

%3CLINGO-SUB%20id%3D%22lingo-sub-2647741%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20a%20SumIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2647741%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys.%20I%20have%20a%20spreadsheet%20with%20invoices%20and%20payment%20over%20the%20last%20two%20years%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20using%20the%20Sumif%20and%20Sumifs%20functions%20to%20isolate%20the%20differt%20products.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20now%20need%20to%20so%20a%20a%20age%20trail%2030%2C60%2C90%20and%20120%2B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20the%20SUMIF%20functions%20I%20understand%20the%20ranges%2C%20but%20how%20do%20i%20add%20the%20criteria%20of%20a%20date%20between%20day%201%20and%20day%2030%20from%20today%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMIF(%24F%242%3A%24F%24573%2C%3CSTRONG%3E%3F%3F%3F%3F%3F%3F%3F%3F%3C%2FSTRONG%3E%2C%24M%242%3A%24M%24573)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20one%20know%20-%20or%20is%20there%20any%20other%20function%20that%20I%20can%20combine%20with%20SUMIF%20to%20get%20the%20figure%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2647741%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2647896%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20SumIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2647896%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128296%22%20target%3D%22_blank%22%3E%40eoliams%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUse%20SUMIFS%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMIF(%24M%242%3A%24M%24573%2C%24F%242%3A%24F%24573%2C%22%26gt%3B%3D%22%26amp%3BTODAY()%2B1%2C%24F%242%3A%24F%24573%2C%22%26lt%3B%3D%22%26amp%3BTODAY()%2B30)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2647903%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20SumIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2647903%22%20slang%3D%22en-US%22%3EHi%20Hans%2C%20i%20will%20try%20that%20and%20let%20you%20know%20how%20it%20goes%20-%20but%20that%20formula%20look%20like%20it%20will%20do%20the%20trick%20-%20thanks%20mate%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi Guys. I have a spreadsheet with invoices and payment over the last two years

 

I have been using the Sumif and Sumifs functions to isolate the differt products.

 

I now need to so a a age trail 30,60,90 and 120+

 

With the SUMIF functions I understand the ranges, but how do i add the criteria of a date between day 1 and day 30 from today?

 

=SUMIF($F$2:$F$573,????????,$M$2:$M$573)

 

Any one know - or is there any other function that I can combine with SUMIF to get the figure?

 

 

5 Replies
best response confirmed by eoliams (Occasional Contributor)
Solution

@eoliams 

Use SUMIFS:

 

=SUMIF($M$2:$M$573,$F$2:$F$573,">="&TODAY()+1,$F$2:$F$573,"<="&TODAY()+30)

Hi Hans, i will try that and let you know how it goes - but that formula look like it will do the trick - thanks mate
Okay - I am missing something - So i get my first error when I try and put the " before the &today().

If I take the " away - it accepts the formula but give a result of 0

btw what is the functionality of the & before today

thanks
I have worked it out finally

=SUMIFS($M$2:$M$573,$F$2:$F$573,"<="&TODAY()-1,$F$2:$F$573,">="&TODAY()-30)

@eoliams 

Ah - you wanted to look at dates before today instead of after today...