SUMIF, with specific search criteria

B

Bglib

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.
 
T

T. Valko

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
 
B

bpeltzer

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)
 
N

Naz

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top