Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude dates from a sum formula
I have a spreadsheet, several, actually, that have columns containing both
numbers and dates. I want to add accross the colums, but exclude the dates. I didn't realize that it was adding the numeric value of the dates in my end formula, and I don't want to have to do the +c12+e12+g12, etc. Any thoughts on making this easier? I tried storing the dates as text, and that seems to be OK but the stupid error checking is driving me NUTS! I would like to have them formatted as dates, just not included. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude dates from a sum formula
Sir,
Jan 1, 2008 is stored as a number, 39,448. If your other numbers (the ones you want to sum) are always smaller than that you could use =sumproduct(--(a1:x1<39448),(a1:x1)). Other than that, I don't know... HTH, Jim "SirMatthew" wrote: I have a spreadsheet, several, actually, that have columns containing both numbers and dates. I want to add accross the colums, but exclude the dates. I didn't realize that it was adding the numeric value of the dates in my end formula, and I don't want to have to do the +c12+e12+g12, etc. Any thoughts on making this easier? I tried storing the dates as text, and that seems to be OK but the stupid error checking is driving me NUTS! I would like to have them formatted as dates, just not included. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude dates from a sum formula
I would insert a row and use it as an indicator--say a new row 1. Then put some
indicator in row 1 of the columns that should be added--say the word "Add". Then I could use =sumif() =sumif($1:$1,"add",3:3) SirMatthew wrote: I have a spreadsheet, several, actually, that have columns containing both numbers and dates. I want to add accross the colums, but exclude the dates. I didn't realize that it was adding the numeric value of the dates in my end formula, and I don't want to have to do the +c12+e12+g12, etc. Any thoughts on making this easier? I tried storing the dates as text, and that seems to be OK but the stupid error checking is driving me NUTS! I would like to have them formatted as dates, just not included. Thanks! -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude dates from a sum formula
I don't want to have to do the +c12+e12+g12, etc.
This formula will sum every other cell starting at cell C12 to cell K12: =SUMPRODUCT(--(MOD(COLUMN(C12:K12),2)=1),C12:K12) This formula is the same as the above but will account for new column insertions before the range: =SUMPRODUCT(--(MOD(COLUMN(C12:K12)-COLUMN(C12),2)=0),C12:K12) If you will *never* insert new columns before the range use the shorter formula. -- Biff Microsoft Excel MVP "SirMatthew" wrote in message ... I have a spreadsheet, several, actually, that have columns containing both numbers and dates. I want to add accross the colums, but exclude the dates. I didn't realize that it was adding the numeric value of the dates in my end formula, and I don't want to have to do the +c12+e12+g12, etc. Any thoughts on making this easier? I tried storing the dates as text, and that seems to be OK but the stupid error checking is driving me NUTS! I would like to have them formatted as dates, just not included. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how I exclude weekend dates | Setting up and Configuration of Excel | |||
Calc days between two dates and exclude leap year days | Excel Worksheet Functions | |||
filtering a list to exclude 2 different dates | Excel Worksheet Functions | |||
Is there a way that I can exclude a formula from being printed? | Excel Discussion (Misc queries) | |||
I need to exclude duplicate patient names for dates of service is. | Excel Discussion (Misc queries) |