Solved IF/THEN using DATEVALUE (conditional sum based on date)

December 21, 2017 at 08:32:59
Specs: Windows 10
I am trying to utilizing a sum of cells in a columns that needs to be cumulative by date...


was trying

=IF(DATEVALUE=TODAY,(SUM(Q9+Q14)),"0")

or

=IF(NOW,(SUM(Q9+Q14)),"0")

with no luck...

any help?


See More: IF/THEN using DATEVALUE (conditional sum based on date)

Reply ↓  Report •

✔ Best Answer
December 21, 2017 at 13:04:42
re: "The "0.00" is appears numerical and therefore valid for subsequent calculations."

Maybe, maybe not. It all depends on the "subsequent calculations".

Enter this in A1: ="0.00"

Enter this in B1: =ISNUMBER(A1)
The result will be FALSE because "0.00" is not a number.

Enter this in C1: =VLOOKUP(0.00,A1,1,0)
The result will be #N/A because "0.00" is not equal to 0.00.

Excel will typically use a value that looks like a number as a number, but not all the time.

My point is, unless there is a specific need to put a Text version of a number in a cell it shouldn't be done.

If you want the value to be a number and you want to be sure that the value will work for any and all "subsequent calculations" that require a number, then don't use the quotes so that the value in the cell is really a number and doesn't just appear to be one.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
December 21, 2017 at 09:28:48
also tried

Got it to work I think using

=if(B7>TODAY(),"0.00",SUM(O7-L7))


Reply ↓  Report •

#2
December 21, 2017 at 10:18:30
Have you tried using the SUMIF() function?
This function adds the value of items which match criteria set by the user.
Something like:

=SUMIF(A1:A6,"="&TODAY(),B1:B6)

Note the "criteria to be matched", IE the TODAY() function,
needs the literal equal sign, so we have to enclose it with quotes.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#3
December 21, 2017 at 10:22:54
First, by using the quotes, "0.00" you will end up with a Text version of 0.00, not the the number version of 0.00. I don't know if that will impact what you are doing, but I just wanted you to be aware that Excel sees the use of quotes as indicating Text.

Second, I don't see how the formula you posted fulfills the "cumulative by date" requirement.

=IF(B7>TODAY(),"0.00",SUM(O7-L7))

1 - SUM(O7-L7) is not really a SUM operation. In "English" that tells Excel to Subtract L7 from O7 and then find the SUM of that result. There really isn't anything to SUM since O7 minus L7 is a single number and the SUM of a single number is that same single number.

Perhaps you meant SUM(L7:O7) which will SUM L7, M7, N7 and O7.

2 - The way that formula is written, the SUM operation is going to execute whenever B7 is greater than TODAY(). It's not going to look at the values referred to by the SUM function and compare them to B7, it's just going to SUM whatever is there. I don't see any "cumulative by date" stuff going on.

Maybe if you posted an example of your data, and the required result based on that data, we could be of more help. Please read the instructions found at the following link before you post example data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
December 21, 2017 at 10:34:04
Thank you all... I am able to use the previous formula I posted without error. The "0.00" is appears numerical and therefore valid for subsequent calculations.

and I do see now that the SUM(O7-L7) is redundant and (O7-L7) suffices more succinctly.

Thank you for your tips


Reply ↓  Report •

#5
December 21, 2017 at 13:04:42
✔ Best Answer
re: "The "0.00" is appears numerical and therefore valid for subsequent calculations."

Maybe, maybe not. It all depends on the "subsequent calculations".

Enter this in A1: ="0.00"

Enter this in B1: =ISNUMBER(A1)
The result will be FALSE because "0.00" is not a number.

Enter this in C1: =VLOOKUP(0.00,A1,1,0)
The result will be #N/A because "0.00" is not equal to 0.00.

Excel will typically use a value that looks like a number as a number, but not all the time.

My point is, unless there is a specific need to put a Text version of a number in a cell it shouldn't be done.

If you want the value to be a number and you want to be sure that the value will work for any and all "subsequent calculations" that require a number, then don't use the quotes so that the value in the cell is really a number and doesn't just appear to be one.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
December 21, 2017 at 15:34:14
ok thank you so much

Reply ↓  Report •

Ask Question