Suming and Subtracting

[O_Junior]

Hey guys,

I need some help. I need a formula that, sum from I7:I1000 if the cells A7:A1000 are filled with "RL1", and G7:G1000 are filled with TODAY() date, but i need to subtract the value of I7:I1000 if the cells J7:J1000 are filled with "OK". I don't know how to do that and I've been trying to come up with a solution for weeks now! Can someone help me?

 

[Jonmo1]

Works just fine for me..

If you're getting 0 it means none of the rows met ALL the criteria of the formula.
Try individual formulas with one criteria each to narrow down the issue.

which of these is not giving the correct result?

=SUMIFS(I7:I1000,A7:A1000,"RL1")
=SUMIFS(I7:I1000,G7:G1000,TODAY())
=SUMIFS(I7:I1000,J7:J1000,"<>OK")


Here it is working on my sheet

Excel Workbook
	A	G	I	J	K	L
7	RL1	11/26/2014	2	othertext		8
8	RL1	11/26/2014	4	OK
9	RL1	11/26/2014	6	moretext
10	RL1	11/26/2014	8	OK
Sheet1

 

[O_Junior]

The TODAY function is not working, maybe because it appears in the cell as DD/MM/YYYY HH:MM? Or because of the VBA that make it appears?

 

[Jonmo1]

Probably this

Or because of the VBA that make it appears?

Can you post the code that puts the dates into column G ?

 

[O_Junior]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, Range("B7:B10000")) Is Nothing Then
If Target <> "" Then
Target.Offset(0, 5) = Now()
Else
Target.Offset(0, 5) = ""
End If
End If

End Sub

 

[Jonmo1]

Target.Offset(0, 5) = Now()

That's the problem, Now returns the Date+Time, not just the date.
But the TODAY() function is just Date, no time.
So indeed, NONE of the dates in column G = TODAY()
(unless the code just happened to run at exactly Midnight...very unlikely)

Try changing Now to Date

This won't correct the dates that already exist in the cells, they'll have to be manually changed to remove the time value.

 

[Marcelo Branco]

I think this is related to this thread
http://www.mrexcel.com/forum/excel-questions/815816-formula-error-2.html#post3985831

So, maybe this formula

=SUMPRODUCT(--($A$7:$A$1000="RL1"),--(INT($G$7:$G$1000)=TODAY()),--($J$7:$J$1000<>"OK"),$I$7:$I$1000)

M.

 

[Jonmo1]

or perhaps

=SUMIFS(I7:I1000,A7:A1000,"RL1",G7:G1000,">="&TODAY(),G7:G1000,"<"&TODAY()+1,J7:J1000,"<>OK")

 

[O_Junior]

That's the problem, Now returns the Date+Time, not just the date.
But the TODAY() function is just Date, no time.
So indeed, NONE of the dates in column G = TODAY()
(unless the code just happened to run at exactly Midnight...very unlikely)

Try changing Now to Date

This won't correct the dates that already exist in the cells, they'll have to be manually changed to remove the time value.

You mean changing NOW to DATE in the VBA? Or the spreadsheet itself?

 

[Jonmo1]

You mean changing NOW to DATE in the VBA? Or the spreadsheet itself?

In the VBA

But if you want/need to keep the time values in column G, see the last 2 posts for workarounds to accomodate the times in G.

 

[Marcelo Branco]

You mean changing NOW to DATE in the VBA? Or the spreadsheet itself?

Have you seen my post above (#16)

By the way, i considered (supposed) that Date and Time are both important to the users.

M.