Home

Right criterion

%3CLINGO-SUB%20id%3D%22lingo-sub-39308%22%20slang%3D%22en-US%22%3ERight%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39308%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20to%20everyone%2C%3C%2FP%3E%3CP%3EI%20can't%20to%20create%20a%20right%20criterion.%3C%2FP%3E%3CP%3EI'd%20like%20to%20create%20a%20criterion%26nbsp%3Bfrom%2001%2F01%2F2015%20to%2031%2F03%2F2015%20but%20my%20formula%20doesn't%20work%20(%22%26gt%3B01%2F01%2F2015%22)%3C%2FP%3E%3CP%3EI%20can%20only%20create%26nbsp%3Ba%20formula%20with%20a%20criterion%20greater%20then%2001%2F01%2F2015.%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-39308%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39326%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39326%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20welcome%2C%20good%20luck%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39325%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39325%22%20slang%3D%22en-US%22%3EThank%20you%20very%20much%20Sergei....%20now%20formula%20is%20ok...%3CBR%20%2F%3EThank%20you%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39323%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39323%22%20slang%3D%22en-US%22%3E%3CP%3EDate%20format%20shall%20be%20as%20in%20your%20regional%20settings%2C%20i%20use%20ISO%20one%20(%22yyyy-mm-dd%22)%2C%20in%20your%20case%20%22dd%2Fmm%2Fyyyy%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39322%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39322%22%20slang%3D%22en-US%22%3E%3CP%3Ei'm%20not%20sure%20what's%20%22date_range%22%20in%20your%20formula%2C%20is%20it%20correct%20name%20of%20the%20range%3F%20See%20my%20example%2C%20it%20works%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20614px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F9898i08F49053C3CCD2AA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SUMIFSsample.JPG%22%20title%3D%22SUMIFSsample.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39319%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39319%22%20slang%3D%22en-US%22%3EI've%20just%20tried%20to%20apply%20that%20correction%20but%20formula%20doesn't%20still%20work.%20%3DSOMMA.PI%C3%99.SE(B1%3AB15%3BA1%3AA15%3B%22%26gt%3B%3D01%2F01%2F2015%22%3Bdate_range%3B%22%26lt%3B%3D31%2F03%2F2015%22)%26nbsp%3B%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39317%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39317%22%20slang%3D%22en-US%22%3E%3CP%3EDavid%2C%20i%20guess%20that's%20SUMIFS.%20First%20i%20see%20you%20use%20mix%20of%20%22%3B%22%20and%26nbsp%3B%22%2C%22%20as%20separator%20in%20formula.%20It%20depends%20on%20your%20regional%20settings%20what%20to%20use%2C%20but%20in%20any%20case%20that%20shall%20be%20always%26nbsp%3B%3CSPAN%3E%22%3B%22%20or%20always%20%22%2C%22.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39316%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39316%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F9897i7BD9D7C523174740%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222017-01-14.png%22%20title%3D%222017-01-14.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39313%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39313%22%20slang%3D%22en-US%22%3E%3DSOMMA.PI%C3%99.SE('FATTURE%20ATTIVE'!H2%3AH1000%3B'FATTURE%20ATTIVE'!A2%3AA1000%3B'PROSPETTO%20GENERALE'!L1%3B'FATTURE%20ATTIVE'!D2%3AD1000%3Bdate_range%2C%26nbsp%3B%22%26gt%3B%3D01%2F01%2F2015%22%2C%20date_range%2C%20%22%26lt%3B%3D31%2F03%2F2015%22)%26nbsp%3B%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39312%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39312%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20of%20all%20thank%20you%20very%20much%20for%20your%20help.%3C%2FP%3E%3CP%3EThis%20is%20my%20formula%3A%20%3DSOMMA.PI%C3%99.SE('FATTURE%20ATTIVE'!H2%3AH1000%3B'FATTURE%20ATTIVE'!A2%3AA1000%3B'PROSPETTO%20GENERALE'!L1%3B'FATTURE%20ATTIVE'!D2%3AD1000%3Bdate_range%2C%26nbsp%3B%22%26gt%3B%3D01%2F01%2F2015%22%2C%20date_range%2C%20%22%26lt%3B%3D31%2F03%2F2015%22).%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20I've%20just%20tried%20to%20use%20your%20suggestion%20but%20this%20formula%20doesn't%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-39310%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-39310%22%20slang%3D%22en-US%22%3E%3CP%3EDavid%2C%20depends%20where%20you%20use%20your%20criterion.%20Usual%20way%20is%20to%20use%20..IFS%20functions%20(COUNTIFS%2C%20SUMIFS%2C%20MINIFS%2C...)%20which%20allow%20to%20use%20multiple%20criterion%26nbsp%3Bwith%20AND%20condition.%20e.g.%20COUNTIFS(date_range%2C%26nbsp%3B%3CSPAN%3E%22%26gt%3B%3D01%2F01%2F2015%22%2C%20date_range%2C%20%22%26lt%3B%3D31%2F03%2F2015%22)%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811468%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811468%22%20slang%3D%22en-US%22%3E%3CP%3E%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%26nbsp%3BI%20hope%20you%20can%20help%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20something%20similar%20with%20MINIFS%20and%20MAXIFS.%20I%20am%20using%20a%20date%20as%20the%20criteria%2C%20which%20is%20held%20in%20a%20cell%2C%20but%20it%20doesn't%20work%20when%20I%20have%20%3D%26gt%3B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%201%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DMINIFS(TablePastData%5BCard%20No%5D%2CTablePastData%5BDate%5D%2CA2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA2%20is%20the%20cell%20with%20the%20date%20in%20and%20this%20works%20perfectly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%202%3A%3C%2FP%3E%3CP%3E%3DMINIFS(TablePastData%5BCard%20No%5D%2CTablePastData%5BDate%5D%2C%22%26gt%3B%3DA2%22)%3C%2FP%3E%3CP%3EThis%20does%20not%20give%20an%20error%2C%20but%20returns%20%220%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20am%20I%20missing%20here%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-811493%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-811493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394688%22%20target%3D%22_blank%22%3E%40JNollett%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20first%20case%20you%20find%20the%20min%20for%20all%20records%20where%26nbsp%3B%3CSPAN%3ETablePastData%5BDate%5D%20is%20equal%20to%20A2.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20second%20case%20where%26nbsp%3BTablePastData%5BDate%5D%20is%20equal%20to%20text%26nbsp%3B%22%26gt%3B%3DA2%22%2C%20I%20guess%20no%20such%20one.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EPlease%20try%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DMINIFS(TablePastData%5BCard%20No%5D%2CTablePastData%5BDate%5D%2C%22%26gt%3B%3D%22%20%26amp%3B%20A2)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-812527%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812527%22%20slang%3D%22en-US%22%3E%3CP%3E%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%3EYou%20are%20a%20genius%20my%20friend.%20Thank%20you%20for%20the%20quick%20response.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-812541%22%20slang%3D%22en-US%22%3ERe%3A%20Right%20criterion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-812541%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394688%22%20target%3D%22_blank%22%3E%40JNollett%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
David Conti
Occasional Contributor

Hi to everyone,

I can't to create a right criterion.

I'd like to create a criterion from 01/01/2015 to 31/03/2015 but my formula doesn't work (">01/01/2015")

I can only create a formula with a criterion greater then 01/01/2015.

Thanks in advance

14 Replies

David, depends where you use your criterion. Usual way is to use ..IFS functions (COUNTIFS, SUMIFS, MINIFS,...) which allow to use multiple criterion with AND condition. e.g. COUNTIFS(date_range, ">=01/01/2015", date_range, "<=31/03/2015") 

First of all thank you very much for your help.

This is my formula: =SOMMA.PIÙ.SE('FATTURE ATTIVE'!H2:H1000;'FATTURE ATTIVE'!A2:A1000;'PROSPETTO GENERALE'!L1;'FATTURE ATTIVE'!D2:D1000;date_range, ">=01/01/2015", date_range, "<=31/03/2015").

As you can see, I've just tried to use your suggestion but this formula doesn't work.

=SOMMA.PIÙ.SE('FATTURE ATTIVE'!H2:H1000;'FATTURE ATTIVE'!A2:A1000;'PROSPETTO GENERALE'!L1;'FATTURE ATTIVE'!D2:D1000;date_range, ">=01/01/2015", date_range, "<=31/03/2015") 

2017-01-14.png

David, i guess that's SUMIFS. First i see you use mix of ";" and "," as separator in formula. It depends on your regional settings what to use, but in any case that shall be always ";" or always ",". 

I've just tried to apply that correction but formula doesn't still work. =SOMMA.PIÙ.SE(B1:B15;A1:A15;">=01/01/2015";date_range;"<=31/03/2015") 

i'm not sure what's "date_range" in your formula, is it correct name of the range? See my example, it works

SUMIFSsample.JPG

Date format shall be as in your regional settings, i use ISO one ("yyyy-mm-dd"), in your case "dd/mm/yyyy"

Thank you very much Sergei.... now formula is ok...
Thank you

You are welcome, good luck

@Sergei Baklan I hope you can help me.

 

I have something similar with MINIFS and MAXIFS. I am using a date as the criteria, which is held in a cell, but it doesn't work when I have =>.

 

Example 1: 

=MINIFS(TablePastData[Card No],TablePastData[Date],A2)

 

A2 is the cell with the date in and this works perfectly.

 

Example 2:

=MINIFS(TablePastData[Card No],TablePastData[Date],">=A2")

This does not give an error, but returns "0".

 

What am I missing here?

 

Highlighted

@JNollett 

In first case you find the min for all records where TablePastData[Date] is equal to A2.

In second case where TablePastData[Date] is equal to text ">=A2", I guess no such one.

Please try

=MINIFS(TablePastData[Card No],TablePastData[Date],">=" & A2)

 

 

 

@Sergei BaklanYou are a genius my friend. Thank you for the quick response.

@JNollett , you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
34 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
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies