Summing numeric values in a row that contains both numbers and dates

**pbeaven** · 07-21-2014, 03:28 PM

I have a row of values that contains dates and numeric values. I want to SUM all of the numeric values within this row but am having problems figuring out how to do this.

I have tried several array formulas, such as {=SUMIF(K17:CC17,isnum(K17:CC17),K17:CC17)} where "isnum" is a VBA function that uses IsNumeric to determine if a cell contains a numeric value (will, according to Excel Help, return FALSE if the cell contains a date value).

I have tried using the built-in function ISNUMBER but that seems to return TRUE when the cell contains a date, so that's of no help.

I am using Excel 2010.

Thanks.

Function isnum(rng As Range) As Boolean
isnum = IsNumeric(rng)
End Function

**AlKey** · 07-21-2014, 03:34 PM

Hi pbeaven and welcome to the forum,

I suggest you upload a sample spreadsheet with about 10-15 rows of data so we could figure our a solution for you.

To upload a file click on Go Advanced at the bottom of your screen then click on PaperClip icon and Add Files, Select File, locate your file and click Open then click Upload and click Done

**Richard Buttrey** · 07-21-2014, 03:37 PM

Hi,

Dates are of course numbers so as you've found you can't use ISNUMBER.

Are the numbers you want to sum within a particular range of values. Or putting it another way is there any other way of distinguishing your date numbers from the numbers to sum? Perhaps one of the adjacent columns has some other identifier, in which case a SUMIFS() might work. You'll need to upload the workbook and comment on the above if you need more specific help.

**FDibbins** · 07-21-2014, 03:41 PM

You need to understand this about dates...a date is just a number indicating how many days have passed since 1/1/1900, so today 7/11/14 is actually 41831. excel formats it into something that we recognise as a date

If you have headings that would ID the columns with dates, you can use that in a SUMIF()

**AlKey** · 07-21-2014, 03:43 PM

Please take a look at this example

	A	B	C
1	10	G	=CELL("format",A1)
2	15	G
3	1/10/2014	D4
4	5/13/2013	D4
5	20	G
6	15	G
7
8	60		=SUMIF(B1:B6,"<>D4",A1:A6)

**sktneer** · 07-21-2014, 03:50 PM

You may also try this to see if this works as per your requirement.

Please Login or Register  to view this content.

**Tony Valko** · 07-21-2014, 03:58 PM

Originally Posted by pbeaven

I have tried several array formulas, such as {=SUMIF(K17:CC17,isnum(K17:CC17),K17:CC17)}

The SUMIF function only works on "straight comparisons".

For example:

=SUMIF(K17:CC17,">0",K17:CC17) Or: =SUMIF(K17:CC17,">0")

You can't "manipulate" a range as you have tried with the isnum.

**Richard Buttrey** · 07-21-2014, 04:01 PM

Originally Posted by AlKey

Please take a look at this example

Nice one. A neat use of the Cell() function.

Just one small improvement since D4 will only pick up dates formatted as dd/mm/yyyy (or perhaps mm/dd/yyyy if you're one of our country cousins on the other side of the pond

) and not dates formatted as say dd-mmm, then

Formula:

Please Login or Register  to view this content.

**AlKey** · 07-21-2014, 04:08 PM

Thank you Richard for catching it. I know I shohld've mentioned that.

**pbeaven** · 07-21-2014, 05:27 PM

Ok, I am attaching a sample spreadsheet and here's what it looks like (I hope):

SampleBook.jpg

One of the problems with using the Cell function is that the "format" value that's returned may vary if the user has changed the format, as someone here pointed out. For example, a numeric cell that includes 2 decimals might return "F2" but if you've selected to have a comma delineate thousands, it will return ",2". That's why the VBA function IsNumeric seems so useful.

As you'll see, each row contains alternating values: a date, then a numeric value, a date, a numeric value, and so on. And you'll see that not all of the cells have values, some will remain blank. I want to be able to sum each row, adding together any numeric values, obviously bypassing any dates. Some of the proposed solutions would work if the values were in a column, but that's not the case.

Hope this helps.

**FDibbins** · 07-21-2014, 05:40 PM

Do those columns have headings?

**Richard Buttrey** · 07-21-2014, 06:11 PM

Hi,

If the numbers to sum are always less than 41640 (Jan 1 2014) then

Formula:

Please Login or Register  to view this content.

or if the dates and values always alternate then

Formula:

Please Login or Register  to view this content.

But I have to ask the obvious question, why are you choosing to record your data in this way and not a more 'normal' layout?

**sktneer** · 07-22-2014, 12:40 AM

Try this......
Refer to your attached sheet in post#10
In O1

Please Login or Register  to view this content.

and copy down.

**sktneer** · 07-22-2014, 12:47 AM

Or more correctly, assuming that all your dates are after the year 2000, you may try this.....

Please Login or Register  to view this content.

**pbeaven** · 07-22-2014, 05:44 PM

Thanks, stkneer. Your solution does work. It is a workaround but since it does what I need, I'll take it!

Richard, I'm not sure what a more "normal" layout would be. We have a variable number of "transactions" (payment amount and date) and they are not consistent in terms of frequency nor chronology. The data is more appropriately thought of as a master/child/grandchild relationship, easily handled with a database system where you can support multiple layers of one-to-many record relationships. It's really not a suitable application for a spreadsheet without a lot of rigamarole, witnessed by the fact that in order to get sktneer's solution to work, we need to "fool" the system. I am surprised that a function can't be used in an array formula - or so it seems. Or my IsNum function would have worked???

**newdoverman** · 07-22-2014, 07:33 PM

Here is a possibility. The date serial numbers are quite large (Oct 3 1954 is 20,000) and unless the currency amounts are in large amounts one can use this:

Formula:

Please Login or Register  to view this content.

If none of the dates is prior to 2010, the 20,000 can be raised to 40,000.

Summing numeric values in a row that contains both numbers and dates

LinkBack

Thread Tools

Rate This Thread

Display

Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Re: Summing numeric values in a row that contains both numbers and dates

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dates Changed to Numeric values

Summing a range of numeric values in the left-most character of text cells?

Summing range of numbers according to variable dates

Summing numeric parts of alphanumeric values

summing numbers within certain dates

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions