Use asterisk with SUMIFS to ignore critieria when needed

[shaked1988]

Hello,

I'm working on an spreadsheet where i want to filled values with dates and summarize it by specific dates.
For example (Example file attached):
In A column there will be dates: 1/1/2017, 1/2/2017, etc
In B column there will be values: 95, 72, etc

Now for the custom summarize function?
in M3 there will be FROM DATE value: 1/1/2017
In M4 there will be TO DATE value: 3/2/2017
In M5 there will be my function:
=SUMIFS(B:B,
A:A,">="&M3,
A:A,"<"&M4)

Until here, it's all good.
Problem is, i want to be able to tell my function to ignore this criteria in some cases. Like when i want summarize all values from 1/1/2017 until the end of time.
So i want to be able to write asterisk in TO DATE cell (M5) and the SUMIFS function will calculate that everything is earlier than * and summarize all values.
I know it can work with COUNTIFS function, but i can't get it work here.
And i don't want to nest IFS, because in the original worksheet i have many criteria, and i don't want to getting lost in 8 rows formula...

Do you have any idea what can i do?

Thank you

[MarvinP]

Hi Shaked and welcome to the forum,

How about this in M5?

=IF(OR(M3="",M4=""),SUM(B:B),(SUMIFS(B:B,A:A,">="&M3,A:A,"<="&M4)))

[shaked1988]

Hi Marvin,

Thank you for your answer.
Sure, that will work. But i wonder if there any way to do that without IF, from two reasons:
1. I want this formula to be more flexiable, so i could enter only TO DATE, leave FROM DATE with asterix only, and get the sum of values just until the TO DATE, no matter from when.
2. i've already have 4 IFS in the full formula and i really don't want to add another one...

And again, thank you for your answer.

[sandy666]

something like this?
(2nd example is with DV)

edit: I forgot, if you want ignore criteria simply delete dates from DV. If you want criteria again, select dates from DVs and add operators.

[JohnTopley]

Try

=SUMPRODUCT((B2:B6)*(A2:A6>=IF(M3="*",DATE(2000,1,1),M3))*(A2:A6<=M4))

Change default date if required

[FlameRetired]

Hi Marvin,
..........................................

2. i've already have 4 IFS in the full formula and i really don't want to add another one...

And again, thank you for your answer.

Perhaps if you uploaded an example with the multi-nested IF formula you currently have the solution would be to reduce the number other IFs?

[bebo021999]

Try:
=SUMIFS(B:B,A:A,">="&IF(M3="*",1,M3),A:A,"<"&IF(M4="*",10^10,M4))