Sum using multiple criteria

[Agnes]

Hi,

I've setup a Sum function following some examples but it
returns an error #VALUE.

Here's my data...I have two tables. Table 1 contains
random dates. I named the range as "date". Table 2 is
the same size and it contains revenue generated on that
day. I named the range as "rev". I want to setup a
function for each month to calculate the sum of revenue
generated in Jan, Feb, Mar, etc.

See example:
Table 1 Table 2

1/4 2/5 3/20 22 15 20
6/15 5/22 12/6 15 6 9
2/3 10/1 11/2 50 3 80

My original function for January is: {=SUM((date>37987)*
(date<38018)*rev)}. 37987 is the numberic format of
1/1/2004, and 38018 is 2/1/2004. It returned an error
#VALUE.

Can you please help me identify what's wrong with this
function, and maybe suggest another function that works
with my data? Your help is greatly appreciated.

 

[Peo Sjoblom]

You get value errors when you calculate a number and text so most likely
some of the values if
not all are text. Also for better readability you can use

date>date(2004,1,1)

or

date>--"01/01/04"

anyway, if you want to calculate the values in rev with the dates you can
use a formula like this

=SUMPRODUCT(--(MONTH(date)=1),rev)