# Sum values based between two dates and another varible

#### slamanager

##### Board Regular
Hello everyone,

I want to sum values in column "C" based on if they fall between and equal to two dates, dates in "A" and a varible in "B"

Column "A" a2-a32 dates of each day in Jan 09 31 days.
Column "B" b2-b32 letters either X Y Z randomly through range some cells may have a letter.
Column "C" Dollar value

in cell D1 Startdate in cell D2 Enddate
Formula located in D3 is the sum of all values falling between the date ranges and by one of the letters X. D4 Y. D5 Z.

Ive looked at sumif and sumproduct but have got really confused especially using inputs as criterias such as the dates any help would be apperciated.

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I've changed the layout slightly, but see if this formula (copied down) does what you want.

Excel Workbook
ABCDE
1DateVariableAmountStart08/01/2009
201/01/2009X4End20/01/2009
302/01/2009Y3X71
403/01/2009Z2Y20
504/01/2009Z3Z14
605/01/2009X4
706/01/2009X43
807/01/2009X2
908/01/2009Z4
1009/01/2009Y5
1110/01/2009Y6
1211/01/2009Z6
1312/01/2009X65
1413/01/2009X
1514/01/2009Z4
1615/01/2009Y4
1716/01/2009Y3
1817/01/2009X3
1918/01/2009X3
2019/01/2009Y2
2120/01/2009Z
2221/01/2009Z2
2322/01/2009X2
2423/01/2009X2
2524/01/2009X2
2625/01/2009Z2
2726/01/2009Y2
2827/01/2009Y24
2928/01/2009Z4
3029/01/2009X5
3130/01/2009X5
3231/01/2009Z
SUM

Amazingly fast response thankyou,

Yes spot on layout. But can i ask what the "--" does in the formula.

Amazingly fast response thankyou,

Yes spot on layout. But can i ask what the "--" does in the formula.
See post #2 here

Ok ive applied it to my actual sheet the only differance is that the letter varible comes from a vlookup from a table.

This formula is to total a large data block so its not on the same sheet ive checked and all the refrences are good and the ranges are correct but im getting N/A returned.

=SUMPRODUCT(--(DataInput!\$G\$3:\$G\$37990>=B32),--(DataInput!\$G\$3:\$G\$37990<=C32),--(DataInput!\$O\$3:\$O\$37990="F"),DataInput!\$F\$3:\$F\$37990)

G = date range
O = Vlookup for a F D or N letter
F = is the value
B32 is the start date
C32 is the end date

Seems to work for me.

Here is my small test data using a simple VLOOKUP for column O.
The results (second screen shot below) should (and does) add the blue rows.

Excel Workbook
FGNOPQR
2
3101/01/20091F1F
4102/01/20093N2D
5103/01/20091F3N
6101/01/20091F
7102/01/20091F
8103/01/20092D
9102/01/20091F
10102/01/20093N
11
DataInput

And my results sheet, using your formula copied directly from your post.

Excel Workbook
BCD
31StartEndResult
3202/01/200903/01/20093
SUMPRODUCT

Strange its not working i'll do some changes and relocate the summary to the same sheet im really puzzled.

Just to ask this isnt a array formula that im missing is it ?

Strange its not working i'll do some changes and relocate the summary to the same sheet im really puzzled.

Just to ask this isnt a array formula that im missing is it ?
No, it is not an array formula.

Check that you don't have any #NA results in your data area. My guess is that the most likely spot would be in column O where your VLOOKUP may be trying to look up something that doesn't exist in the lookup table. Like this:

Excel Workbook
FGNOPQR
3101/01/20091F1F
4102/01/20093N2D
5103/01/20091F3N
6101/01/20091F
7102/01/20091F
8103/01/20094#N/A
9102/01/20091F
10102/01/20093N
DataInput

Excel Workbook
BCD
31StartEndResult
3202/01/200903/01/2009#N/A
SUMPRODUCT

YEs there are some N/A values in that date range for the FDN lookup i will use iserror and check if that sorts out the issue.

Replies
1
Views
274
Replies
5
Views
296
Replies
1
Views
74
Replies
4
Views
263
Replies
6
Views
313

1,218,651
Messages
6,143,711
Members
450,502
Latest member
sakaria_123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back