Counting values within date range

L

Leo Skywalker

Board Regular

Joined

Jun 24, 2017

Messages

222

Office Version

1. 365
2. 2016
3. 2013

Platform

1. Windows
2. Web

• Jul 13, 2017

• #2

You could try:

=SUMPRODUCT(($A$1:$A$50000=D1)*($B$1:$C$50000))

SUMPRODUCT can't take full columns, so I am using 50000 rows; you might change it to a value that better suits your needs.

 

Last edited: Jul 13, 2017

Upvote 0

K

krispatterson

Board Regular

Joined

Apr 28, 2017

Messages

51

• Jul 14, 2017

• #3

Hey Leo


Thanks for that, but it doesn't seem to do what I need...

does the original requirement make sense?

 

Upvote 0

steve the fish

Well-known Member

Joined

Oct 20, 2009

Messages

8,894

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2017

• #4

The dates in column D such as Nov 16.... are they true dates?? Click Nov 16. What appears in the formula bar?

 

Upvote 0

R

Repush

Board Regular

Joined

Sep 21, 2015

Messages

133

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2017

• #5

try

=SUMPRODUCT(--($A$1:$A$5000>=D1)*($B$1:$C$5000))-SUMPRODUCT(--($A$1:$A$5000>=EDATE(D1,1))*($B$1:$C$5000))

 

Last edited: Jul 14, 2017

Upvote 0

K

krispatterson

Board Regular

Joined

Apr 28, 2017

Messages

51

• Jul 14, 2017

• #6

steve, dates are all 01/11/16, 01/12/16 etc etc

& repush, that doesn't work unfortunately.


I'll try and recreate my spreadsheet below, in basic form:

WORKBOOK 1

A	B	C
01/11/16
01/12/16
01/01/17
01/02/17

<tbody>
</tbody>

WORKBOOK 2

A	B	C
04/11/16	1	5
10/11/16	1	45
15/11/16	8	16
14/11/16	4	10
01/12/16	6	1
22/11/16	4	6

<tbody>
</tbody>

I want a formula that:

> looks at Workbook 2
> looks at Column A and finds all instances of a date in November
> If it finds a November instance, it then adds Columns B & C together.


So, I've used the edate function previously to find the number of instances of a date range in November before (as per 1st message). What I now need it to do is an extra calculation when it find those instances.


Does that make sense??

 

Upvote 0

R

Repush

Board Regular

Joined

Sep 21, 2015

Messages

133

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2017

• #7

it makes sense . . . and that's what the sumproduct-formula should do . .

can you explain "doesn't work"? (errors, (unexpected) values, . . )

What should the result in Workbook 1 look like?

 

Upvote 0

K

krispatterson

Board Regular

Joined

Apr 28, 2017

Messages

51

• Jul 14, 2017

• #8

Repush said:

it makes sense . . . and that's what the sumproduct-formula should do . .

can you explain "doesn't work"? (errors, (unexpected) values, . . )

What should the result in Workbook 1 look like?

Click to expand...

Thanks Repush.


So
- result in Workbook 1, Column B (next to 01/11/16) should be 100 (so, sum of all numbers in Workbook 2, Column B & C excl the 01/12/16 values)
- result in Workbook 2, Column B (next to 01/12/16) should be 7 (so, sum of all numbers in Workbook 2, Column B & C excl the 01/11/16 values)


I think that makes sense

 

Upvote 0

R

Repush

Board Regular

Joined

Sep 21, 2015

Messages

133

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2017

• #9

and that is exactly the result I get . .
but I had tot translate it from Dutch (; to , and so on) so I might have made a typo

 

Upvote 0

R

Repush

Board Regular

Joined

Sep 21, 2015

Messages

133

Office Version

1. 365

Platform

1. Windows

• Jul 14, 2017

• #10

I checked . . no typo . .
try to put it in the same workbook (2)

 

Upvote 0