Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range that IS NOT NULL
Can someone help me with this.. I know it has to be easier than I am making
it for myself. I am trying to SUM a column contingent on another having a date entered in it (all on the same ROW). I am putting the sum in a colum off to the right of them both. I don't want to sum the rows without a date and the dates are always different. =IF(C3="date",I3*0.8,0) Something like this and I will be draggin this formula down Column "J", this is based on two columns the condition is the date, the dollars is in column "I" Please help!! -- JESSY |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range that IS NOT NULL
Try this:
=COUNT(C3)*I3*0.8 NB: true Excel dates are really just *numbers* formatted to look like dates. So, if the cells might contain a date or a number then you'll have to be more specific about just checking for a date. You'd have to refine it to look for dates within a date range. -- Biff Microsoft Excel MVP "JKELSTONE" wrote in message ... Can someone help me with this.. I know it has to be easier than I am making it for myself. I am trying to SUM a column contingent on another having a date entered in it (all on the same ROW). I am putting the sum in a colum off to the right of them both. I don't want to sum the rows without a date and the dates are always different. =IF(C3="date",I3*0.8,0) Something like this and I will be draggin this formula down Column "J", this is based on two columns the condition is the date, the dollars is in column "I" Please help!! -- JESSY |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range that IS NOT NULL
I've tried all of these..
=IF(C3="date",I3*0.8,0) =IF(C3="NOTNULL",I3*0.8,0) =IF(C3="number",I3*0.8,0) =IF(C3="value",I3*0.8,0) Column C is a date column only all the way down, Column I is a dollars column all the way down. I want to create a formula in J3 (Column "J" is my formula column) that calculates 80% of the dollars in column I but ONLY if there has been a date entered in column C for that row. IF there has NOT been a date entered in C3 and that cell is blank then I want "J3" to say "0". I am thinking it should be very simple like IF C3 is NOT NULL then, complete the formula, if it is NULL, then it's 0. I am not concerened with the date itself just the fact that some kind of value is entered in that cell then the formula is TRUE and it gives me the sum of I3*0.8 The Count function did not work, thank you though. "JKELSTONE" wrote: Can someone help me with this.. I know it has to be easier than I am making it for myself. I am trying to SUM a column contingent on another having a date entered in it (all on the same ROW). I am putting the sum in a colum off to the right of them both. I don't want to sum the rows without a date and the dates are always different. =IF(C3="date",I3*0.8,0) Something like this and I will be draggin this formula down Column "J", this is based on two columns the condition is the date, the dollars is in column "I" Please help!! -- JESSY |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range that IS NOT NULL
Hi,
Try this =isnumber(C3)*I3*0.8 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JKELSTONE" wrote in message ... Can someone help me with this.. I know it has to be easier than I am making it for myself. I am trying to SUM a column contingent on another having a date entered in it (all on the same ROW). I am putting the sum in a colum off to the right of them both. I don't want to sum the rows without a date and the dates are always different. =IF(C3="date",I3*0.8,0) Something like this and I will be draggin this formula down Column "J", this is based on two columns the condition is the date, the dollars is in column "I" Please help!! -- JESSY |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range that IS NOT NULL
This does work, if I take my columns and paste them values only into a blank
spreadsheet, then format them Dates and Currency like they are in my Master. So, there must be something with my Master Spreadsheet that is messing with the formula Thank you!! -- JESSY "Ashish Mathur" wrote: Hi, Try this =isnumber(C3)*I3*0.8 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JKELSTONE" wrote in message ... Can someone help me with this.. I know it has to be easier than I am making it for myself. I am trying to SUM a column contingent on another having a date entered in it (all on the same ROW). I am putting the sum in a colum off to the right of them both. I don't want to sum the rows without a date and the dates are always different. =IF(C3="date",I3*0.8,0) Something like this and I will be draggin this formula down Column "J", this is based on two columns the condition is the date, the dollars is in column "I" Please help!! -- JESSY |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range that IS NOT NULL
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JKELSTONE" wrote in message ... This does work, if I take my columns and paste them values only into a blank spreadsheet, then format them Dates and Currency like they are in my Master. So, there must be something with my Master Spreadsheet that is messing with the formula Thank you!! -- JESSY "Ashish Mathur" wrote: Hi, Try this =isnumber(C3)*I3*0.8 -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "JKELSTONE" wrote in message ... Can someone help me with this.. I know it has to be easier than I am making it for myself. I am trying to SUM a column contingent on another having a date entered in it (all on the same ROW). I am putting the sum in a colum off to the right of them both. I don't want to sum the rows without a date and the dates are always different. =IF(C3="date",I3*0.8,0) Something like this and I will be draggin this formula down Column "J", this is based on two columns the condition is the date, the dollars is in column "I" Please help!! -- JESSY |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum a range that IS NOT NULL
=COUNT(C3)*I3*0.8
The Count function did not work =isnumber(C3)*I3*0.8 This does work Hmmm... It's not possible that the COUNT version *didn't* work while the ISNUMBER version did work. Both of those formulas do the same thing but in a different way. COUNT will return either 1 or 0 ISNUMBER will return either TRUE or FALSE In a calculation TRUE and FALSE are the equivalent of 1 and 0. So, both formulas will return exactly the same result under *every* condition. -- Biff Microsoft Excel MVP "JKELSTONE" wrote in message ... I've tried all of these.. =IF(C3="date",I3*0.8,0) =IF(C3="NOTNULL",I3*0.8,0) =IF(C3="number",I3*0.8,0) =IF(C3="value",I3*0.8,0) Column "C" is a date column only all the way down, Column "I" is a dollars column all the way down. I want to create a formula in "J3" (Column "J" is my formula column) that calculates 80% of the dollars in column "I" but ONLY if there has been a date entered in column "C" for that row. IF there has NOT been a date entered in C3 and that cell is blank then I want "J3" to say "0". I am thinking it should be very simple like IF C3 is NOT NULL then, complete the formula, if it is NULL, then it's 0. I am not concerened with the date itself just the fact that some kind of value is entered in that cell then the formula is TRUE and it gives me the sum of I3*0.8 The Count function did not work, thank you though. "JKELSTONE" wrote: Can someone help me with this.. I know it has to be easier than I am making it for myself. I am trying to SUM a column contingent on another having a date entered in it (all on the same ROW). I am putting the sum in a colum off to the right of them both. I don't want to sum the rows without a date and the dates are always different. =IF(C3="date",I3*0.8,0) Something like this and I will be draggin this formula down Column "J", this is based on two columns the condition is the date, the dollars is in column "I" Please help!! -- JESSY |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Failed to save table attributes of (null) into (null). | Excel Discussion (Misc queries) | |||
replace null cells within a range to 0 | Excel Discussion (Misc queries) | |||
COUNTIF says Null = Blank but Blank < Null | Excel Worksheet Functions | |||
cell value based on null/not null in another cell | Excel Worksheet Functions | |||
Specify a null value in an Excel Database criteria range | Excel Worksheet Functions |