Solved

# Sumifs(excel < date and if blank)

Posted on 2012-08-24
492 Views
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
Question by:MarkVrenken

LVL 24

Expert Comment

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

LVL 1

Author Comment

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

LVL 24

Expert Comment

try """" in place of ""

so ,""""}
0

LVL 24

Expert Comment

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

LVL 24

Expert Comment

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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 24

Expert Comment

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

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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 24

Expert Comment

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

LVL 1

Author Comment

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

LVL 24

Expert Comment

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

LVL 1

Author Comment

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

LVL 24

Expert Comment

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

LVL 1

Author Comment

I send it to you. Thanks for helping!
0

LVL 24

Accepted Solution

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

LVL 1

Author Closing Comment

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

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows anâ€¦
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Datâ€¦
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a â€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.