• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

Sumifs(excel < date and if blank)

Dear experts i have a complex function that im writing.

=Sum(SUMIFS(Deals!$K:$K,Deals!$F:$F,{"US","EU"},Deals!BB:BB,'Deals Calc'!D2,Deals!$M:$M,{"<>electric","<> toys","<> car*"},Deals!$J:$J,'Deals Calc'!$B2,Deals!AX:AX,FALSE,

Deals!N:N,{"=<"&YEAR(NOW())-7,""}))


where the last code is the part that doesn't work.

Open in new window

what i am trying to do with the last part of the function is to get all the values where the deal date is below now - 6 years. +
the fields in that range that are empty

I hope someone can help me with this,,

Cheers,

Mark Vrenken.
0
MarkVrenken
Asked:
MarkVrenken
  • 10
  • 9
1 Solution
 
SteveCommented:
if the date is in dateformat then the year(now())-7 will return a number (2005)

if the date is a date (and not a year) you would need (now()-(7*365))
0
 
MarkVrenkenAuthor Commented:
thanks for the reply

The date is a year so that's working but the blanks aren't included. Only if i remove the datepart the blanks are being calculated. So there is the problem/mistake in my formula
0
 
SteveCommented:
try """" in place of ""

so ,""""}
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
SteveCommented:
also less than or equal to is "<=" not "=<" that will cause error too

It may be simpler to use a pivot table to generate the data you require.
0
 
SteveCommented:
also {"<>electric","<> toys","<> car*"} may work out as being incorrect.

this may be equivalent to Not toys OR Not electric OR Not car*

which as electric is "not toys" this may sum up rather than exclude.
0
 
MarkVrenkenAuthor Commented:
Thanks i cannot test this till after the,weekend but will do and then see if i can accept your solution, i hope so! In any case thanks for the help! btw i put some dummy text in so the
 <> section doesn't make any sense. sharply seen though!
0
 
MarkVrenkenAuthor Commented:
Still can't get it to work. When i try it it says the formula you typed contains an error and it highlights the " after <=
Deals!N:N,{"<="&YEAR(NOW()-6),""""}))
What am i doing wrong.

Thanks in advance.
0
 
SteveCommented:
take out the double quotes... but keep the "<="

The double quotes is likely wrong. Should be "".
0
 
MarkVrenkenAuthor Commented:
like this? Deals!N:N,{"<="&YEAR(NOW())-6,""}
Sadly gives me the same error
0
 
MarkVrenkenAuthor Commented:
Deals!N:N,{"<=&YEAR(NOW())-6",""}))
this gives me no errors but only returns the fields where the entry is ""
0
 
MarkVrenkenAuthor Commented:
Sorry this is not true, it sometimes returns the fields where "" and sometimes it doesn't....
0
 
SteveCommented:
The Year now formula has the quotes in the wrong place
Dropping the "blank" may still work as blank = 0 (which is less than year(now)-6)
try:
{"<="&YEAR(NOW())-6,""}
or just
"<="&YEAR(NOW())-6
0
 
MarkVrenkenAuthor Commented:
Sadly still not... I really don't understand. I would do the same but it just doesn't work.
0
 
SteveCommented:
try swapping the formula for "<=2006"
Does this still error?
At least we can eliminate the Year(now()) portion.

If at all possible, could you post the workbook? or an apporximation of it.
0
 
MarkVrenkenAuthor Commented:
Yeah, i think i made a stupid mistake.

I thought the first part worked. but i guess it doesn't So it has something to do with the sum(sumifs() part. I guess it doesnt sum up the whole part. I can't give you the real data(corporate data) but i can make something with dummy data.
0
 
SteveCommented:
Fully understand about not sending "Real Data".

If you would like to post driect to me rather than posting here can mail me direct (see attached)
Text-Document.txt
0
 
MarkVrenkenAuthor Commented:
I send it to you. Thanks for helping!
0
 
SteveCommented:
OK, response sent.
It is not the best, but it should do the job.
0
 
MarkVrenkenAuthor Commented:
Thank you very much, it is indeed less elegant as hoped but does the trick perfectly:). Thanks for your time and all the effort you put in!
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 10
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now