Counting values within date range

[krispatterson]

Hi guys

Pretty simple one for you lot I'm sure!


Column A - 1000's of dates
Column B - £'s
Column C - £'s

Column D - list of dates (ie Nov 16, Dec 16, Jan 17 etc)



I'm currently using the following formula to count the number of instances of dates within a range:
=COUNTIFS(!$A:$A,">="&D1,!$A:$A,"<"&EDATE(D1,1))

I need to amend it so that my formula:
> looks in Column A and pulls out all dates within a range (so, as the above, only look at date instances in Nov 16)
> if Column A is in Nov 16, the figure from Columns B & C are added together.


Does that make sense? Let me know if it's doable, thanks guys!

Kris

 

[Leo Skywalker]

I see the problem now, all dates in Workbook 2 have days... while all dates in Workbook 1 don't, so they default to 1, that makes the comparison fail.

 

[Leo Skywalker]

OK, We have to take only the year and the month, so try this:

	A	B	C	D	E	F
1	04/11/2016	1	5		01/11/2016	100
2	10/11/2016	1	45		01/12/2016	7
3	15/11/2016	8	16
4	14/11/2016	4	10
5	01/12/2016	6	1
6	22/11/2016	4	6

<colgroup><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>

F1:

=SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*$B$1:$C$5000))

<tbody>
</tbody>

 

[krispatterson]

OK, We have to take only the year and the month, so try this:

	A	B	C	D	E	F
1	04/11/2016	1	5		01/11/2016	100
2	10/11/2016	1	45		01/12/2016	7
3	15/11/2016	8	16
4	14/11/2016	4	10
5	01/12/2016	6	1
6	22/11/2016	4	6

<tbody>
</tbody>

F1:

=SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*$B$1:$C$5000))

<tbody>
</tbody>

Woohoo!! Finally got there... After much frustration etc as to why it was failing, it was because there was 3 instances of non-numerical data in Column B! So, I was getting a #VALUE error... Eventually found the rouge data and have amended, it now works! Thank you so much for all your help people...

 

[krispatterson]

OK, so one follow-up question.

I now want to filter that data down so it only adds up based on a range of names.

So Leo, from your example below, table is actually like this:

	A	B	C	D	E	F	G
1	04/11/2016	1	5	Chris	01/11/2016	100	30
2	10/11/2016	1	45	Bob	01/12/2016	7
3	15/11/2016	8	16	Bob
4	14/11/2016	4	10	Chris
5	01/12/2016	6	1	Jack
6	22/11/2016	4	6	Chris

<tbody>
</tbody>

Original formula is =SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*$B$1:$C$5000))

This original counts the total amount of numbers.

I now need it to also look at Column D and only count the values if the name is specified. I've done this in other places by adding this onto the end: ,!$D:$D,"chris" , but that hasn't worked this time!

My full, not working formula in G1 reads =SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*$B$1:$C$5000,$D:$D,"chris")


i get a #VALUE! error - does that just mean I need to find the rogue data again?? Or have I missed something with that formula?

Thanks

 

[Repush]

it's sumproduct (hit F1 for info)
add another product:
*(D1:D5000="Chris")

 

[krispatterson]

it's sumproduct (hit F1 for info)
add another product:
*(D1:D5000="Chris")

Thanks Repush - I thought that would be nice'n'simple, but it's not working.

formula now is =SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*$B$1:$C$5000)*(D1:D5000="Chris")

but that gives me the result as without *(D1:D5000="Chris")


Am I being thick??

 

[Repush]

just a ")"
=SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*$B$1:$C$5000 *(D1:D5000="Chris"))

 

[MARK858]

Try reversing the last 2 criteria..

Excel Workbook
	A	B	C	D	E	F
1	04/11/2016	1	5	Chris	01/11/2016	30
2	10/11/2016	1	45	Bob	01/12/2016
3	15/11/2016	8	16	Bob
4	14/11/2016	4	10	Chris
5	01/12/2016	6	1	Jack
6	22/11/2016	4	6	Chris
Sheet1

Excel 2010

Cell Formulas
Range	Formula
F1	=SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*(D1:D5000="Chris")*$B$1:$C$5000)

 

[Repush]

it's the misplaced ) . . in a product the order is irrelevant --> 1 x 2 x 3 = 1 x 3 x 2
NB all product need to be inside the sumproduct parentheses

 

[krispatterson]

Try reversing the last 2 criteria..

Sheet1

	A	B	C	D	E	F
1	04/11/2016	1	5	Chris	01/11/2016	30
2	10/11/2016	1	45	Bob	01/12/2016
3	15/11/2016	8	16	Bob
4	14/11/2016	4	10	Chris
5	01/12/2016	6	1	Jack
6	22/11/2016	4	6	Chris

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula F1 =SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*(D1:D5000="Chris")*$B$1:$C$5000) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

IT'S ALIVE!!!

Mark, thank you - you've come in last minute and saved the day!


Thanks as well Repush, much appreciated!