Sum using multiple criteria

  • Thread starter Thread starter Agnes
  • Start date Start date
A

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.
 
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)
 
Back
Top