Formula that sum numeric and text

**bountifulgrace** · 10-26-2006, 02:24 PM

I have a spreadsheet with data for the last 3 years by day.

Column A contains the date and columns B–C contains numeric and text data.

I am looking for a formula that will sum the data in Column B-C based on a beginning and ending date. I need do this for each week of the last 3 years.

I did this formula and it gave me a value reference.

=SUMPRODUCT(('FY 2006'!$A$8:$A$550=C$4)*('FY 2006'!$A$8:$A$550<=C$5)*('FY 2006'!$B$8:$B$550))

C4 = 12/25/05
C5 = 1/1/06.

Any help provided is appreciated.

Thanks.

**Carim** · 10-26-2006, 03:05 PM

Hi,

If both your date fields are formatted in a consistent way ...
you are left with a minor change to your formula, the operator -- for your column B :

=SUMPRODUCT(('FY 2006'!$A$8:$A$550=C$4)*('FY 2006'!$A$8:$A$550<=C$5)*--('FY 2006'!$B$8:$B$550))

HTH
Carim

**MrShorty** · 10-26-2006, 03:13 PM

If column B contains text, those text values will be the source of your error value. How do you want to treat the text values?

Unsolicited observation. Unless you've made an error in entering your function, you don't need 2 conditions to capture your data. Your statement is equivalent to, "Find all days that are equal to Christmas '05 AND before New Years '06". In essence, it will only find days that are equal to Chrismas '05, rendering the 2nd condition meaningless. I would suggest you double check your logic to make sure your function is capturing the data you want.

**bountifulgrace** · 10-27-2006, 09:35 AM

Thanks Carim and MrShorty, however, I am still have a problem with the formula.

The sumproduct formula with the -- did not help.

Essentially what I am trying to find is a formula for "between" that basiscally says if a date is between this date (beginning) and that date (ending, then sum the range.

I would like to ignore the text values in Column B or count it as a zeros and sum just the numbers, but the fact that the file contains numbers and text in B makes it difficult.

Thanks.

**MrShorty** · 10-27-2006, 12:06 PM

Interesting:

I put this variation of the formula (smaller range) =sumproduct(--(a3:a13>=a1),--(a3:a13<=a2),b3:b13) and it worked just fine with text values in b3:b13, treated them as zeroes. Basically, I just substituted the *'s with ,'s. According to Excel help,

SUMPRODUCT treats array entries that are not numeric as if they were zeros.

. I guess this "feature" of the function only works if sumproduct is allowed to do the multiplying rather than the user. I guess it has to do with the fact that, this version results in three arrays that sumproduct works on. Your original version results in a single array (containing error values) that sumproduct works on. Don't understand it completely, but see if that helps.