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

# 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.
``````
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
• 10
• 9
1 Solution

Commented:
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

Author 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

Commented:
try """" in place of ""

so ,""""}
0

Commented:
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

Commented:
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

Author 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

Author 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.

0

Commented:
take out the double quotes... but keep the "<="

The double quotes is likely wrong. Should be "".
0

Author Commented:
like this? Deals!N:N,{"<="&YEAR(NOW())-6,""}
Sadly gives me the same error
0

Author Commented:
Deals!N:N,{"<=&YEAR(NOW())-6",""}))
this gives me no errors but only returns the fields where the entry is ""
0

Author Commented:
Sorry this is not true, it sometimes returns the fields where "" and sometimes it doesn't....
0

Commented:
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

Author Commented:
Sadly still not... I really don't understand. I would do the same but it just doesn't work.
0

Commented:
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

Author 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

Commented:
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

Author Commented:
I send it to you. Thanks for helping!
0

Commented:
OK, response sent.
It is not the best, but it should do the job.
0

Author 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

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