Sum values based between two dates and another varible

slamanager

Board Regular
Joined
Apr 20, 2007
Messages
129
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

slamanager

Board Regular
Joined
Apr 20, 2007
Messages
129
Amazingly fast response thankyou,

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

slamanager

Board Regular
Joined
Apr 20, 2007
Messages
129

ADVERTISEMENT

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.
 

slamanager

Board Regular
Joined
Apr 20, 2007
Messages
129
=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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

slamanager

Board Regular
Joined
Apr 20, 2007
Messages
129
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 ?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

slamanager

Board Regular
Joined
Apr 20, 2007
Messages
129
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,524
Messages
5,602,169
Members
414,509
Latest member
Cdavis7078

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top