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

 

[MARK858]

it's the misplaced ) . . in a product the order is irrelevant

Isn't it strange then that reversing the criteria gives the correct answer of 30 without moving the )

Excel Workbook
	A	B	C	D	E	F	G
1	04/11/2016	1	5	Chris	01/11/2016	30	100
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)
G1	=SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*$B$1:$C$5000)*(D1:D5000="Chris")

 

[krispatterson]

Isn't it strange then that reversing the criteria gives the correct answer of 30 without moving the )

Sheet1

	A	B	C	D	E	F	G
1	04/11/2016	1	5	Chris	01/11/2016	30	100
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) G1 =SUMPRODUCT((YEAR($A$1:$A$5000)&MONTH($A$1:$A$5000)=YEAR(E1)&MONTH(E1))*$B$1:$C$5000)*(D1:D5000="Chris") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Wish I understood it properly, but as long as it works... Thanks again chaps :D

 

[MARK858]

Wish I understood it properly, but as long as it works... Thanks again chaps :D

Both the formula I posted and the one Repush posted work (and the one Repush posted is technically more correct)

 

[Repush]

Isn't it strange then that reversing the criteria gives the correct answer of 30 without moving the )

'reversing the criteria' was actually moving the criterium inside the sumproduct-parentheses

anyhow . . mr. Patterson is happy

 

[MARK858]

'reversing the criteria' was actually moving the criterium inside the sumproduct-parentheses

I know, when I responded to the question from the OP I didn't give an explanation as to what it was doing differently and why swapping the criteria worked (probably should have). I just gave a different method to achieve the same result (nasty as it was).

anyhow . . mr. Patterson is happy

Perfectly agree (and hopefully with your explanation the OP understands a bit more now)
Have a good day

 

[Aladin Akyurek]

@krispatterson

=COUNTIFS(A:A,">="&D1-DAY(D1)+1,A:A,"<="&EOMONTH(D1,0))

where D1 is a date in November of a ceratin year, say November 2016. Preferably: 1-Nov-2016.

 

[krispatterson]

Once again, thanks all... & another follow up, sorry!!


I thought I'd got the hang of SUMPRODUCT, but clearly not...

Chart is as follows:

	A	B	C	D	E	F	G	H	I	J
1	04/11/2016	1	5	Chris	01/11/2016	4	1.1	30	100
2	10/11/2016	1	45	Bob	01/12/2016	4	0.9
3	15/11/2016	8	16	Bob		3	1
4	14/11/2016	4	10	Chris		3	1.5
5	01/12/2016	6	1	Jack		1	0.75
6	22/11/2016	4	6	Chris		4	2

<tbody>
</tbody>

From Mark858 below, the formulas I used (amended slightly cos I've moved some values around)...

Worksheet Formulas

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

<tbody>
</tbody>

What I want in J1 is a formula that does the same as I1, but:
> also looks at F and if F=4, adds up and averages the figures from G.


So, the outcome of J1 should be 1.1 (G1) + 2 (G6) / 2 = 1.55




I'm just not good enough at Excel to get it to do all these things together!! Always really struggle to 'add' formulas together...

Can you help please? :D Thanks