Jan 14 2017
08:26 AM
- last edited on
Jul 25 2018
09:23 AM
by
TechCommunityAP
Jan 14 2017
08:26 AM
- last edited on
Jul 25 2018
09:23 AM
by
TechCommunityAP
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
Jan 14 2017 09:11 AM
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")
Jan 14 2017 09:21 AM
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.
Jan 14 2017 09:22 AM
Jan 14 2017 09:37 AM
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 ",".
Jan 14 2017 09:53 AM
Jan 14 2017 10:01 AM - edited Jan 14 2017 10:02 AM
i'm not sure what's "date_range" in your formula, is it correct name of the range? See my example, it works
Jan 14 2017 10:04 AM
Date format shall be as in your regional settings, i use ISO one ("yyyy-mm-dd"), in your case "dd/mm/yyyy"
Jan 14 2017 10:07 AM
Aug 19 2019 11:10 AM
@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?
Aug 19 2019 11:21 AM
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)
Aug 20 2019 03:56 AM
@Sergei BaklanYou are a genius my friend. Thank you for the quick response.