CODEWITHSUNDEEP
excelexcel-formula
Tino
Tino

Reputation: 3368

SUMIF formula with date criteria not working

I have a sum if statement (in german):

=SUMMEWENNS(Rawdata!K2:K3446;Rawdata!I2:I3446;"bezahlt";Rawdata!A2:A3446;">= 04.03.2013 00:00";Rawdata!A2:A3446;"<= 10.03.2013 23:59")

I'm not sure why it doesn't work, cause I have checked the cells for formatting (TT.MM.JJJJ hh:mm)

The Shees where it gets the data from Looks like this:

======= A ======      == I ==  ==== K ====
04.03.2013 22:00      bezahlt     10,20 €

The formula gives me back "0" even though it should give me back the 10,20

Where is the mistake?

EDIT the formula editor gives me back the following:

enter image description here

What do I have to edit my formula to do get the right results???

2nd EDIT

Ok the formula works now. Now I only need to be able to copy it into a field via VBA I seem to have a syntax problem:

Cells(5, fieldextsales).FormulaLocal = "=SUMMEWENNS(Rawdata!K2:K" & maxnumrows & ";Rawdata!I2:I" & maxnumrows & ";""bezahlt"";Rawdata!A2:A" & maxnumrows & ";"">= "&DATWERT(""& weekstart &" 23:59")";Rawdata!A2:A" & maxnumrows & ";""<= "&DATWERT(""& weekend & " 23:59"))"

Thx for the help

Upvotes: 1

Views: 2569

Answers (2)

chuff
chuff

Reputation: 5866

If your cell date amounts are stored as formatted date amounts, change your formula to:

=SUMMEWENNS(Rawdata!K2:K3446;Rawdata!I2:I3446;"bezahlt";Rawdata!A2:A3446;">="&DATWERT("04.03.2013 00:00");Rawdata!A2:A3446;"<="&DATWERT("10.03.2013 23:59"))

Upvotes: 1

Brian Camire
Brian Camire

Reputation: 4825

As an alternative to @chuff's answer that doesn't rely on DATEVALUE to interpret the date format, you might try...

=SUMMEWENNS(Rawdata!K2:K3446;Rawdata!I2:I3446;"bezahlt";Rawdata!A2:A3446;">="&DATUM(2013;3;4);Rawdata!A2:A3446;"<"&DATUM(2013;3;11))

...where DATUM is the German name for the worksheet function named DATE in English.

Upvotes: 0

Related Questions