SUMIF, with specific search criteria
 

I have the following data in a table (6 rows, 3 columns):

A .02 2/1/07
B .05 3/1/07
A .02 4/1/07
B .05 5/1/07
A .02 6/1/07
B .05 7/1/07

I want to find the sum of the 2nd column, but between certain dates. I want
to find the sum of B between 3/1/07 - 5/1/07. The answer would equal .10

The only way I know how to do this is through a sumif formula, which will
give me .15 as an answer to my previous question. I am unsure how to add the
date range into my search criteria.

Any and all help is greatly appreciated!
Regards.

SUMIF, with specific search criteria
 

Try one of these:

=SUMPRODUCT(--(A1:A6="B"),--(C1:C6=DATE(2007,3,1)),--(C1:C6<=DATE(2007,5,1)),B1:B6)

Or, better to use cells to hold the criteria:

E1 = B
F1 = 3/1/2007
G1 = 5/1/2007

=SUMPRODUCT(--(A1:A6=E1),--(C1:C6=F1),--(C1:C6<=G1),B1:B6)

Format as NUMBER 2 decimal places

Biff

"Bglib" wrote in message
...
I have the following data in a table (6 rows, 3 columns):

A .02 2/1/07
B .05 3/1/07
A .02 4/1/07
B .05 5/1/07
A .02 6/1/07
B .05 7/1/07

I want to find the sum of the 2nd column, but between certain dates. I
want
to find the sum of B between 3/1/07 - 5/1/07. The answer would equal .10

The only way I know how to do this is through a sumif formula, which will
give me .15 as an answer to my previous question. I am unsure how to add
the
date range into my search criteria.

Any and all help is greatly appreciated!
Regards.

SUMIF, with specific search criteria
 

You can do this with two SUMIF functions... the first to calculate
everything on/after 3/1, the second to calculate everything after 5/1. Then
just subtract the two to get everything in the range:
=SUMIF(A:A,"="&DATE(2007,3,1),B:B)-SUMIF(A:A,""&DATE(2007,5,1),B:B)


"Bglib" wrote:

I have the following data in a table (6 rows, 3 columns):

A .02 2/1/07
B .05 3/1/07
A .02 4/1/07
B .05 5/1/07
A .02 6/1/07
B .05 7/1/07

I want to find the sum of the 2nd column, but between certain dates. I want
to find the sum of B between 3/1/07 - 5/1/07. The answer would equal .10

The only way I know how to do this is through a sumif formula, which will
give me .15 as an answer to my previous question. I am unsure how to add the
date range into my search criteria.

Any and all help is greatly appreciated!
Regards.

SUMIF, with specific search criteria
 

Try

=SUM(IF($A$1:$A$6=A11,IF($C$1:$C$6=B11,IF($C$1:$C $6<=C11,$B$1:$B$6))))

the formula is an array so after you write it dont press enter, you have to
press Ctrl+shft+enter

where
A11= A or B
B11= start date
C11 = end date


--

_______________________
Naz,
London


"Bglib" wrote:

I have the following data in a table (6 rows, 3 columns):

A .02 2/1/07
B .05 3/1/07
A .02 4/1/07
B .05 5/1/07
A .02 6/1/07
B .05 7/1/07

I want to find the sum of the 2nd column, but between certain dates. I want
to find the sum of B between 3/1/07 - 5/1/07. The answer would equal .10

The only way I know how to do this is through a sumif formula, which will
give me .15 as an answer to my previous question. I am unsure how to add the
date range into my search criteria.

Any and all help is greatly appreciated!
Regards.